pipe rows

------------------------------------------------------------------ Sometimes maybe you are forbided to create any temporary table when doing the coding. If so, perhaps you should first keep the data in a record and then regard this record as a table for doing some operations like join by some means. ------------------------------------------------------------------ Here is a way recommended to handle it : --Firstly create a record test_rec and test_typ which is the table of test_rec type test_rec is record (id number ,name varchar2(10)); type test_typ is table of test_rec; v_test test_typ := test_typ(); --Then generate data for v_test v_test.delete; for i in 1..10 loop v_test.extend; v_test.id := i; v_test.name := chr(i); end loop; --Then create a function keeping the data which can be specified as a table create function gen_tbl return test_typ pipelined as begin for i in 1..v_test.count loop pipe row(v_test(i)); end loop; return; end gen_tbl; --At last you could doing any operation specifying v_test as a table select a.* from TABLE(gen_tbl(v_test)) a where a.id = 3; ---Example : create or replace package p_test as type test_rec is record (id number ,name varchar2(10)); type test_typ is table of test_rec; v_test test_typ := test_typ(); function gen_tbl return test_typ pipelined; procedure main_exec; end; / create or replace package body p_test as function gen_tbl return test_typ pipelined as begin for i in 1..v_test.count loop pipe row(v_test(i)); end loop; return; end gen_tbl; procedure main_exec is type rc is ref cursor; v_cur rc; id number; name varchar2(10); begin v_test.delete; for i in 1..26 loop v_test.extend; v_test(i).id := i+64; v_test(i).name := chr(i+64); end loop; open v_cur for select a.* from TABLE(gen_tbl) a; for i in 1..26 loop fetch v_cur into id,name; dbms_output.put_line(id||' with name is '||name); end loop; end; end; /