example: PIPELINED function

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 6 Feb 2004 08:58:01 -0500
Message-ID: <EKKdnTXKD_wLAL7dRVn_iw@comcast.com>

i posted this example in a cdo.misc in response to another question, but thought it might be helpful to isolate it here:

• create generic varchar2 type for return value

create type vc4000tbl as table of varchar2(4000);

• function to return csv list as a table

create or replace function as_table (

    ip_arr in varchar2
    )
return vc4000tbl pipelined
is

  idx      number default 1;
  dlen     number default length(ip_arr);
  slen     number;
  sep      varchar2(1) default ',';

begin
  while idx < dlen
  loop

     slen := instr(ip_arr,sep,idx);
     if slen = 0
     then
        slen := dlen+1;
     end if;

  pipe row (substr(ip_arr,idx,slen-idx));

     idx := slen+1;
  end loop;

  return;

end as_table;

• use the function in a select statement

select column_value
from table (as_table('this,that,the other')) Received on Fri Feb 06 2004 - 07:58:01 CST