pipe rows

------------------------------------------------------------------Sometimes maybe you are forbided to create any temporarytable when doing the coding.If so, perhaps you should first keep the data in a recordand then regard this record as a table for doing some operationslike 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_rectype 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_testv_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 tablecreate function gen_tblreturn test_typ pipelinedasbegin 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 tableselect a.*from TABLE(gen_tbl(v_test)) awhere a.id = 3; ---Example :create or replace package p_testastype 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_tblreturn test_typ pipelined;procedure main_exec;end;/create or replace package body p_testasfunction gen_tblreturn test_typ pipelinedasbegin 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);beginv_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 loopfetch v_cur into id,name;dbms_output.put_line(id||' with name is '||name);end loop;end;end;/