CODEWITHSUNDEEP
oracle10g
fazlook1
fazlook1

Reputation: 139

Functions Pipelined

Could you please explain this to me: I have the following in a Package Specification and not the body using Oracle 10g.

TYPE t_fraud_ext IS TABLE OF FI_RPT.FI_Fraud_OBJ;


FUNCTION fraud_ext_Sql
(   schema_name  IN VARCHAR2
, select_beginning_business_date     IN DATE     – Start Date
, select_thru_business_date  IN DATE     – End Date
, select_beginning_business_time     IN VARCHAR2     – Start Time
, select_thru_business_time  IN VARCHAR2     – End Time
) RETURN VARCHAR2;



FUNCTION fraud_ext
(   schema_name  IN VARCHAR2
, select_beginning_business_date     IN DATE     – Start Date
, select_thru_business_date  IN DATE     – End Date
, select_beginning_business_time     IN VARCHAR2     – Start Time
, select_thru_business_time  IN VARCHAR2     – End Time
) RETURN t_fraud_ext PIPELINED;

How these are related ? never worked with Pipelined function.

I would also love a very detailed example on how to use pipeline function in a Package Specification / Body.

Thank you

Upvotes: 1

Views: 1066

Answers (1)

DazzaL
DazzaL

Reputation: 21993

give that you only have the spec, we cannot tell if fraud_ext_Sql is called by fraud_ext, but it's irrelevant in the context of a pipelined function.

to use a pipelined function you start with a base array / nested table type. eg:

SQL> create type test_typ as object (id number, txt varchar2(20));
  2  /

Type created.

SQL> create type test_tab as table of test_typ;
  2  /

Type created.

so test_tab will be used in the pipelined output. we could have a scalar array/nested table too. e.g. this would also be valid to use with a pipelined function:

SQL> create type test_tab as table of varchar2(20);
  2  /

Type created.

once you have your base type, you define the function as pipelined. In the following example, i've encapsulated the function in a package, but this is not a requirement; stand-alone functions can be used too. 

SQL> create package test_pkg
  2  as
  3    function get_data(p_id number)
  4    return test_tab pipelined;
  5  end;
  6  /

Package created.

the pipelined keyword is the key. the package body is like the below:

SQL> create package body test_pkg
  2  as
  3
  4    function get_data(p_id number)
  5    return test_tab pipelined
  6    is
  7    begin
  8      for idx in 1..p_id
  9      loop
 10        pipe row(test_typ(idx, dbms_random.string('x', 2)));
 11      end loop;
 12    end get_data;
 13
 14  end;
 15  /

Package body created.

the PIPE ROW command pumps a row back to the client. This (unlike regular functions) returns the row to the client immediately (i.e. it wont wait for the whole set to materialize before sending the rows back) you'll see rows coming back in batches controlled by your arraysize setting in your client. This has the advantage that Oracle doesn't need to hold the whole array in memory before sending the data back to the client.

so to call a pipelined function you use the table function like so:

SQL> select *
  2    from table(test_pkg.get_data(10));

        ID TXT
---------- --------------------
         1 3B
         2 AM
         3 1J
         4 36
         5 8I
         6 BM
         7 LS
         8 ON
         9 5Z
        10 D7

10 rows selected.

if you had a scalar array, like I assuded to before, the pipe row command just has the value directly without any type name in it:

SQL> create type test_tab as table of varchar2(20);
  2  /

Type created.

SQL>
SQL> create package test_pkg
  2  as
  3    function get_data(p_id number)
  4    return test_tab pipelined;
  5  end;
  6  /

Package created.

SQL> create package body test_pkg
  2  as
  3
  4    function get_data(p_id number)
  5    return test_tab pipelined
  6    is
  7    begin
  8      for idx in 1..p_id
  9      loop
 10        pipe row(dbms_random.string('x', 2));
 11      end loop;
 12    end get_data;
 13
 14  end;
 15  /

Package body created.

SQL> select *
  2    from table(test_pkg.get_data(10));

COLUMN_VALUE
--------------------
GS
MJ
PF
D6
NG
WO
22
MV
96
8J

Upvotes: 3

Related Questions