sql - How to return table types (collections) from procedure with sysrefcursor PLSQL -

i want return table types sysrefcursor in procedure. have defined record , table types:

create or replace type "file_record" object  ( id number(30,0), create_date  timestamp(6) );  create or replace type "file_table" table of file_record;  create or replace type "msg_record" object  ( id    number(30,0), create_date   timestamp(6) );  create or replace type "msg_table" table of msg_record; 

and wrote procedure:

   create or replace procedure file_msg        (i_date in date,         o_cur out sys_refcursor)         l_file file_table;     l_msg msg_table;      begin        l_file := file_table ();       l_msg := msg_table ();        select  file_record              (fi.id,               fi.create_date )        bulk collect l_file        files fi        fi.create_date between i_date , i_date +1;         in l_file.first..l_file.last            loop             select  msg_record               (me.id,                me.create_date )             bulk collect l_msg             messages me             file_id = l_file(i).id;            end loop;  end file_msg;  

so how can return table types l_msg , l_file sysrefcursor procedure?

i have return table types ( collections) refcursor , not columns real tables how explained in question:

how store result of function return sysrefcursor?

what xing suggested. tried solution question doesn't me.

you can as:

 create or replace procedure file_msg (i_date   in     date,                                       o_cur     out sys_refcursor,                                       p_cure    out sys_refcursor)      l_file   file_table;    l_msg    msg_table; begin    l_file := file_table ();    l_msg := msg_table ();     select file_record (fi.id, fi.create_date)      bulk collect l_file      files fi     fi.create_date between i_date , i_date + 1;     open p_cure select * table(l_file);     in l_file.first .. l_file.last    loop       select msg_record(me.id, me.create_date)         bulk collect l_msg         messages me        file_id = l_file(i).id;    end loop;     open o_cur select * table(l_msg);  end file_msg;