René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Using bulk collect with execute immediate in Oracle

create or replace type varchar_number as object (
   v varchar2(10),
   i number
)
/
create or replace type t_varchar_number as table of varchar_number
/
create table vn (
   v varchar2(10),
   i number
);
insert into vn values ('uno',1);
insert into vn values ('due',2);
insert into vn values ('tre',3);

commit;
create or replace function fct_t return t_varchar_number as
   ret t_varchar_number;
begin
   execute immediate 'select varchar_number(v,i) from vn'
      bulk collect into ret;

   return ret;
end;
/
set serveroutput on size 10000

declare
   x t_varchar_number;
begin
   x := fct_t;

   for r in (select * from table(cast(x as t_varchar_number))) loop
     dbms_output.put_line(r.v || ', ' || r.i);
   end loop;

end;
/
uno, 1
due, 2
tre, 3