create or replace type RT_PRODUCTLISTITEM as object( 
  PRODUCTID integer,  
  QUANTITY integer, 
  VATFREE char(1)); 

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; 

call DBMS_OUTPUT.put_line('Executing first query')

select 
  FNC_PRODUCTLISTITEM as PRODUCTQUANTITYLINE 
from 
  dual

call DBMS_OUTPUT.put_line(chr(13)||'Executing second query')

select 
  p.PRODUCTLISTITEM.PRODUCTID, 
  p.PRODUCTLISTITEM.QUANTITY, 
  p.PRODUCTLISTITEM.VATFREE 
from 
 (select 
    FNC_PRODUCTLISTITEM as PRODUCTLISTITEM 
  from 
    dual) p

call DBMS_OUTPUT.put_line(chr(13)||'Work-around')

create or replace type TT_PRODUCTLISTITEM is table of RT_PRODUCTLISTITEM; 

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; 

select 
  p.PRODUCTID, 
  p.QUANTITY, 
  p.VATFREE 
from 
  FNC_PRODUCTLIST() p

drop function FNC_PRODUCTLIST

drop function FNC_PRODUCTLISTITEM

drop type TT_PRODUCTLISTITEM

drop type RT_PRODUCTLISTITEM