Pipelined Table Function

>>Script Language and Platform: Oracle PL/SQL
The purpose of this code is to provide an example of the new pipelined and parallel PL/SQL table functions available in Oracle9i Database.

Author: Claudiu Ariton

Script Code: create table phones
(cust_id number(12) primary key,
phone1 varchar2(50),
phone2 varchar2(50),
phone3 varchar2(50),
phone4 varchar2(50),
phone5 varchar2(50)
)
/
— insert data into table
begin
 for i in 1..1000000 loop
  insert into phones values (i,i+1,i+2,i+3,i+4,i+5);
  if mod(i,1000)=0 then
    commit;
  end if;
 end loop;
 commit;
end;
/
create or replace package TypePkg is
 type PhoneRecord is RECORD
(cust_id number(12),
 phone1 varchar2(50),
 phone2 varchar2(50),
 phone3 varchar2(50),
 phone4 varchar2(50),
 phone5 varchar2(50));
 type PhoneCursorType is REF CURSOR return PhoneRecord;
end;
/
create or replace type PhoneResult as object
( cust_id  number(12),
phone varchar2(50) );
/
create or replace type PhoneResultTab as table of PhoneResult;
/
create or replace function GetPhones (inrecs IN TypePkg.PhoneCursorType)
return PhoneResultTab pipelined
order inrecs by ( cust_id )
parallel_enable ( partition inrecs by Hash ( cust_id ) )
as
 currec PhoneResult := PhoneResult(null, null);
 initrec TypePkg.PhoneRecord;
begin
loop
 fetch inrecs into initrec.cust_id, initrec.phone1, initrec.phone2,
initrec.phone3, initrec.phone4, initrec.phone5;
 exit when inrecs%NOTFOUND;  
 currec.cust_id:=initrec.cust_id;
 if initrec.phone1 is not null then
  currec.phone:=initrec.phone1;
  pipe row(currec);
 end if;
 if initrec.phone2 is not null then
  currec.phone:=initrec.phone2;
  pipe row(currec);
 end if;
 if initrec.phone3 is not null then
  currec.phone:=initrec.phone3;
  pipe row(currec);
 end if;
 if initrec.phone4 is not null then
  currec.phone:=initrec.phone4;
  pipe row(currec);
 end if;
 if initrec.phone5 is not null then
  currec.phone:=initrec.phone5;
  pipe row(currec);
 end if;
end loop;
close inrecs;
return;    
end;
/
*****************************************************************
Test: 
select * from
TABLE(CAST(GetPhones(cursor(select
cust_id,phone1,phone2,phone3,phone4,phone5 from phones)) AS
PhoneResultTab));
/ 
select /*+ USE_NL(vt) */ cust_id,phone from
TABLE(CAST(GetPhones(cursor(select
cust_id,phone1,phone2,phone3,phone4,phone5 from phones)) AS
PhoneResultTab)) vt
where exists
(select /*+ USE_NL(p) */ cust_id,phone1 from phones p where
p.cust_id=vt.cust_id and p.PHONE1=vt.phone)
/

Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…

Disclaimer Continued

Back to Database Journal Home