create or replace type RT_PRODUCTLISTITEM as object(
PRODUCTID integer,
QUANTITY integer,
VATFREE char(1));
Type created.
create or replace function FNC_PRODUCTLISTITEM return RT_PRODUCTLISTITEM is
V_RESULT RT_PRODUCTLISTITEM := RT_PRODUCTLISTITEM(652783, 100, 'Y');
begin
DBMS_OUTPUT.PUT_LINE('Executing function');
return V_RESULT;
end;
Function created.
call DBMS_OUTPUT.put_line('Executing first query')
Statement processed.
Executing first query
select
FNC_PRODUCTLISTITEM as PRODUCTQUANTITYLINE
from
dual
| PRODUCTQUANTITYLINE | [unsupported data type] |
|---|
call DBMS_OUTPUT.put_line(chr(13)||'Executing second query')
Statement processed.
Executing second query
select
p.PRODUCTLISTITEM.PRODUCTID,
p.PRODUCTLISTITEM.QUANTITY,
p.PRODUCTLISTITEM.VATFREE
from
(select
FNC_PRODUCTLISTITEM as PRODUCTLISTITEM
from
dual) p
| PRODUCTLISTITEM.PRODUCTID | PRODUCTLISTITEM.QUANTITY | PRODUCTLISTITEM.VATFREE | 652783 | 100 | Y |
|---|
call DBMS_OUTPUT.put_line(chr(13)||'Work-around')
Statement processed.
Work-around
create or replace type TT_PRODUCTLISTITEM is table of RT_PRODUCTLISTITEM;
Type created.
create or replace function FNC_PRODUCTLIST return TT_PRODUCTLISTITEM is
V_RESULT TT_PRODUCTLISTITEM := TT_PRODUCTLISTITEM();
begin
DBMS_OUTPUT.PUT_LINE('Executing work-around function');
V_RESULT.extend();
V_RESULT(1) := RT_PRODUCTLISTITEM(652783, 100, 'Y');
return V_RESULT;
end;
Function created.
select
p.PRODUCTID,
p.QUANTITY,
p.VATFREE
from
FNC_PRODUCTLIST() p
| PRODUCTID | QUANTITY | VATFREE | 652783 | 100 | Y |
|---|
drop function FNC_PRODUCTLIST
Function dropped.
drop function FNC_PRODUCTLISTITEM
Function dropped.
drop type TT_PRODUCTLISTITEM
Type dropped.
drop type RT_PRODUCTLISTITEM
Type dropped.