Tom ..I am giving you some information in return..

this should make you feel better...

</code> http://news.bbc.co.uk/1/hi/scotland/1263510.stm <code>

Thanks for such a complete reply. I am inspired to go find a problem to try out this solution. ;)

Can you see other benefits in pipelined functions? What about using Java functions to generate table-like data?

It all sounds like another tool for the box, which is great news.

If you were to write a function (not pipelined) that returned a varray of objects, then you

SELECT * from Table(cast(my_func as myTableType))

Doesn't this give you the same functionality as pipelined functions?? Please can you explain the difference.

Tom,  Can you show me what I am doing wrong here.  I am tring to write a Pipelined function so I can pass a comma delimmited list of IDs to a procedure to be used as an IN list.

SQL> CREATE OR REPLACE TYPE MYNUMTYPE AS TABLE OF NUMBER
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION GET_NUM_LIST(
  2     P_STR   IN   VARCHAR2 )
  3     RETURN MYNUMTYPE PIPELINED
  4  AS
  5     L_STR         LONG      DEFAULT P_STR || ',';
  6     L_N           NUMBER;
  7     V_ID          NUMBER;
  8     V_MYNUMTYPE   MYNUMTYPE;
  9  BEGIN
 10     LOOP
 11        L_N := INSTR( L_STR, ',' );
 12        EXIT WHEN( NVL( L_N, 0 ) = 0 );
 13        V_ID := NVL( LTRIM( RTRIM( SUBSTR( L_STR, 1, L_N - 1 ))), -9999999999 );
 14  
 15        IF V_ID != -9999999999 THEN
 16           V_MYNUMTYPE := MYNUMTYPE( V_ID );
 17           PIPE ROW( V_MYNUMTYPE );
 18        END IF;
 19  
 20        L_STR := SUBSTR( L_STR, L_N + 1 );
 21     END LOOP;
 22  
 23     RETURN;
 24  END;
 25  /
Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION GET_NUM_LIST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
17/10    PL/SQL: Statement ignored
17/20    PLS-00382: expression is of wrong type

Thanks 

Syntax and reasons to use pipelined functions very useful

Here's an example of how useful pipelined function can be: select * from table(ourpipelinedfuction) , in a Custom SQL in Discoverer EUL, gives us the results as a table. Now if we could just figure out a way to pass it a parameter ....

Hi Tom,

When we pipe the row from pipelined table function, PL/SQL engine doesn't pass it to the consumer immediatley instead passes it in batches for the performance reasons.

The statement in ORACLE pl/sql document is as follows:

"In PL/SQL, the PIPE ROW statement causes a table function to pipe a row and continue processing. The statement enables a PL/SQL table function to return rows as soon as they are produced. (For performance, the PL/SQL runtime system provides the rows to the consumer in batches.)"

My question is, is there anyway we can control the batch size?

Regards,
Badari

Tom,

But we can control the array size only if we are using a varray. What if we are using the pl/sql table (nested table)?

Thanks
Praveen

Wow, Tom, you showered me with knowledge....thankyou very much.

The explanation is as clear as it could be.

I am investigating the performance of pipelined table functions by joining a table function with a physical table (database table) versus two tables join together, and found out the first case is slower than second one.

I ran the two statements in a test procedure 1000 times and profiled them:

First (table function join with a table): 7.91 seconds
Second (two actual table joins): 1.15 seconds

I noticed that the first case has a full table scan, and I could not avoid that. I tried different hints, but still the same. In the second case, indexes were used.

The difference could be well caused by the full table scan.

Could you please provider an example to prove mine is wrong, and table function performs better.

Thanks,

Hi Tom,

Thanks for your quick response! My perception is pipelined table function is faster in general than the queries through regular tables, but may be just the response time.

I am trying to get better performance by using table functions, and that's my goal. But it seems there is no real advantage for my case at least. It's a cool feature, and it has its advantages as you spelled out.

I am interested in knowing if people experience performance gain by using the table functions in their applications. Again, each application is different, but if someone could post a simple test scenario to show a better performance in using table functions.

Thanks again!

Your answer will save days of my work in pursuing something which I don't know exactly to where it really should be applied.

Thanks,

Hi Tom, i have learned everything what i need to know about pipeline. Is it possible to pass parameter through a pipeline function?
like SELECT * FROM TABLE(FUNC_PIPELINE(p_mrchno,p_crd_no));

How can i manage a pipe lined function to retrive data from mainframe? because ,here you have to use dbms_hs_passthrough, not a simple cursor.
Thanks in advance

Hi Tom, i have learned everything what i need to know about pipeline. Is it possible to pass parameter through a pipeline function?
like SELECT * FROM TABLE(FUNC_PIPELINE(p_mrchno,p_crd_no));

How can i manage a pipe lined function to retrive data from mainframe? because ,here you have to use dbms_hs_passthrough, not a simple cursor.
Thanks in advance

Tom

You are simply amazing......

There are tons of things to learn from a guru like you !!!

Rgds

SVS

Tom I have:
CREATE OR REPLACE TYPE pending_issues_type AS OBJECT (
ME_SEQ                                             NUMBER,
PERS_SEQ                                           NUMBER,
NAME                                               VARCHAR2(4000),
SSN                                                VARCHAR2(9),
RATE                                               VARCHAR2(10),
START_DATE                                         DATE,
TYPE_ID                                            VARCHAR2(1),
TYPE_DESC                                          VARCHAR2(30),
PROVIDER                                           VARCHAR2(4000),
FINALIZED_DATE                                     DATE,
LOG_DTG                                            DATE,
SSN_LAST_4                                         VARCHAR2(4),
SSN_LAST_2                                         VARCHAR2(9),
FMPC_ID                                            VARCHAR2(2),
SORT_FMPC_ID                                       VARCHAR2(2),
STATUS_GROUP                                       VARCHAR2(1),
DEPT_NAME                                          VARCHAR2(20),
CMD_NAME                                           VARCHAR2(100),
WC_NAME                                            VARCHAR2(20),
DIV_NAME                                           VARCHAR2(20),
PAYGRADE_CLASS                                     VARCHAR2(4),
CMD_DOD_UIC                                        VARCHAR2(8),
CMD_ASSIGNMENT                                     VARCHAR2(32),
VISITOR_IND                                        VARCHAR2(1) )
/

CREATE OR REPLACE TYPE pending_issues_set_type AS TABLE OF pending_issues_type;
/
SHOW ERROR

@me_lib.pks

SHOW ERROR

@me_lib.pkb

SHOW ERROR

CREATE OR REPLACE VIEW ME_PENDING_ISSUES_VW (
    ME_SEQ,
    PERS_SEQ,
    NAME,
    SSN,
    RATE,
    START_DATE,
    TYPE_ID,
    TYPE_DESC,
    PROVIDER,
    FINALIZED_DATE,
    LOG_DTG,
    SSN_LAST_4,
    SSN_LAST_2,
    FMPC_ID,
    SORT_FMPC_ID,
    STATUS_GROUP,
    DEPT_NAME,
    CMD_NAME,
    WC_NAME,
    DIV_NAME,
    PAYGRADE_CLASS,
    CMD_DOD_UIC,
    CMD_ASSIGNMENT,
    VISITOR_IND )
AS
 SELECT * FROM TABLE ( me_lib.get_pending_issues() );

The function get_pending_issues looks like:
FUNCTION get_pending_issues RETURN pending_issues_set_type
PIPELINED
IS
  cursor c_issues is
         SELECT         mp.me_seq,
                        mp.pers_seq,
                        hs_lib.get_pers_name(mp.pers_seq),
                        mp.me_ssn,
                        mp.me_rate,
                        mp.me_start_date,
                        mp.me_type_id,
                        mt.type_desc,
                        sams_system.getprovidername(mp.prov_seq),
                        mp.finalized_date,
                        ml.log_dtg,
                        rhv.ssn_last_4,
                        rhv.ssn_last_2,
                        rhv.fmpc_id,
                        rhv.sort_fmpc_id,
                        rhv.status_group,
                        rhv.dept_name,
                        rhv.cmd_name,
                        rhv.wc_name,
                        rhv.div_name,
                        rhv.paygrade_class,
                        rhv.cmd_dod_uic,
                        rhv.cmd_assignment,
                        rhv.visitor_ind
                   FROM me_report_header_vw rhv,
                        me_pers             mp,
                        me_prov_log         ml,
                        me_type             mt
                  WHERE mp.finalized_date IS NULL
                    AND mp.me_type_id      != 'R'
                    AND mp.log_seq         = ml.log_seq
                    AND mp.me_type_id      = mt.type_id
                    AND mp.me_seq          = rhv.me_seq
                UNION
                 SELECT mp.me_seq,
                        mp.pers_seq,
                        hs_lib.get_pers_name(mp.pers_seq),
                        mp.me_ssn,
                        mp.me_rate,
                        mm.mer_date,
                        decode(mp.me_type_id, 'S','Z','R'),
                        decode(mp.me_type_id, 'S','MER(via SOAPP)','MER'),
                        sams_system.getprovidername(mp.prov_seq),
                        mm.finalized_date,
                        ml.log_dtg,
                        rhv.ssn_last_4,
                        rhv.ssn_last_2,
                        rhv.fmpc_id,
                        rhv.sort_fmpc_id,
                        rhv.status_group,
                        rhv.dept_name,
                        rhv.cmd_name,
                        rhv.wc_name,
                        rhv.div_name,
                        rhv.paygrade_class,
                        rhv.cmd_dod_uic,
                        rhv.cmd_assignment,
                        rhv.visitor_ind
                   FROM me_report_header_vw rhv,
                        me_pers             mp,
                        me_prov_log         ml,
                        me_type             mt,
                        me_mer              mm
    -- line 981   WHERE mm.finalized_date IS NULL 
                    AND mp.log_seq         = ml.log_seq
                    AND mp.me_type_id      = mt.type_id
                    AND mp.me_seq          = rhv.me_seq;
BEGIN
      FOR x IN c_issues LOOP

             PIPE ROW( x );
      END LOOP;
             RETURN;
END get_pending_issues;

but when I try to compile the package I get
SQL> @me_lib.pkb
DOC>***********************************************************************
DOC>* ME_LIB.PKB                                                          *
DOC>* As of 06 Oct 2005 @ 09:16.                                          *
DOC>*---------------------------------------------------------------------*
DOC>*                                                                     *
DOC>**********************************************************************/
Creating Package Body 'ME_LIB'
DOC>* ME_LIB package body
DOC>*************************************************************************

Warning: Package Body created with compilation errors.

SQL> show error
Errors for PACKAGE BODY ME_LIB:

LINE/COL ERROR
-------- -----------------------------------------------------------------
981/14   PL/SQL: Statement ignored
981/24   PLS-00382: expression is of wrong type

I even tried without defining a explicit cursor in did the 
for x in ( select .... ) loop but got the same error

any ideas? 

......
CREATE OR REPLACE VIEW ME_PENDING_ISSUES_VW (
ME_SEQ,
PERS_SEQ,
NAME,
SSN,
RATE,
START_DATE,
TYPE_ID,
TYPE_DESC,
PROVIDER,
FINALIZED_DATE,
LOG_DTG,
SSN_LAST_4,
SSN_LAST_2,
FMPC_ID,
SORT_FMPC_ID,
STATUS_GROUP,
DEPT_NAME,
CMD_NAME,
WC_NAME,
DIV_NAME,
PAYGRADE_CLASS,
CMD_DOD_UIC,
CMD_ASSIGNMENT,
VISITOR_IND )
AS
SELECT * FROM TABLE ( me_lib.get_pending_issues() );

is this bad? In your opinion of course.

.... makes sense to me.
But for grins if I had this:
SQL> desc pending_issues_set_type
 pending_issues_set_type TABLE OF PENDING_ISSUES_TYPE
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------
 ME_SEQ                                             NUMBER
 PERS_SEQ                                           NUMBER
 NAME                                               VARCHAR2(4000)
 SSN                                                VARCHAR2(9)
 RATE                                               VARCHAR2(10)
 START_DATE                                         DATE
 TYPE_ID                                            VARCHAR2(1)
 TYPE_DESC                                          VARCHAR2(30)
 PROVIDER                                           VARCHAR2(4000)
 FINALIZED_DATE                                     DATE
 LOG_DTG                                            DATE
 SSN_LAST_4                                         VARCHAR2(4)
 SSN_LAST_2                                         VARCHAR2(9)
 FMPC_ID                                            VARCHAR2(2)
 SORT_FMPC_ID                                       VARCHAR2(2)
 STATUS_GROUP                                       VARCHAR2(1)
 DEPT_NAME                                          VARCHAR2(20)
 CMD_NAME                                           VARCHAR2(100)
 WC_NAME                                            VARCHAR2(20)
 DIV_NAME                                           VARCHAR2(20)
 PAYGRADE_CLASS                                     VARCHAR2(4)
 CMD_DOD_UIC                                        VARCHAR2(8)
 CMD_ASSIGNMENT                                     VARCHAR2(32)
 VISITOR_IND                                        VARCHAR2(1)

why would this:
<snip>
cursor c_issues is
         SELECT         mp.me_seq,
                        mp.pers_seq,
                        hs_lib.get_pers_name(mp.pers_seq) pername,
                        mp.me_ssn,
                        mp.me_rate,
                        mp.me_start_date,
                        mp.me_type_id,
                        mt.type_desc,
                        sams_system.getprovidername(mp.prov_seq) provname,
                        mp.finalized_date,
                        ml.log_dtg,
                        rhv.ssn_last_4,
                        rhv.ssn_last_2,
                        rhv.fmpc_id,
                        rhv.sort_fmpc_id,
                        rhv.status_group,
                        rhv.dept_name,
                        rhv.cmd_name,
                        rhv.wc_name,
                        rhv.div_name,
                        rhv.paygrade_class,
                        rhv.cmd_dod_uic,
                        rhv.cmd_assignment,
                        rhv.visitor_ind
                   FROM me_report_header_vw rhv,
                        me_pers             mp,
                        me_prov_log         ml,
                        me_type             mt
                  WHERE mp.finalized_date IS NULL
                    AND mp.me_type_id      != 'R'
                    AND mp.log_seq         = ml.log_seq
                    AND mp.me_type_id      = mt.type_id
                    AND mp.me_seq          = rhv.me_seq
                UNION
                 SELECT mp.me_seq,
                        mp.pers_seq,
                        hs_lib.get_pers_name(mp.pers_seq),
                        mp.me_ssn,
                        mp.me_rate,
                        mm.mer_date,
                        decode(mp.me_type_id, 'S','Z','R'),
                        decode(mp.me_type_id, 'S','MER(via SOAPP)','MER'),
                        sams_system.getprovidername(mp.prov_seq),
                        mm.finalized_date,
                        ml.log_dtg,
                        rhv.ssn_last_4,
                        rhv.ssn_last_2,
                        rhv.fmpc_id,
                        rhv.sort_fmpc_id,
                        rhv.status_group,
                        rhv.dept_name,
                        rhv.cmd_name,
                        rhv.wc_name,
                        rhv.div_name,
                        rhv.paygrade_class,
                        rhv.cmd_dod_uic,
                        rhv.cmd_assignment,
                        rhv.visitor_ind
                   FROM me_report_header_vw rhv,
                        me_pers             mp,
                        me_prov_log         ml,
                        me_type             mt,
                        me_mer              mm
                  WHERE mm.finalized_date IS NULL
            AND mm.me_seq          = mp.me_seq
                    AND mp.log_seq         = ml.log_seq
                    AND mp.me_type_id      = mt.type_id
                    AND mp.me_seq          = rhv.me_seq;
BEGIN
      FOR x IN c_issues LOOP
             PIPE ROW( pending_issues_set_type(
                    x.me_seq,
                        x.pers_seq,
                        x.pername,
                        x.me_ssn,
                        x.me_rate,
                        x.me_start_date,
                        x.me_type_id,
                        x.type_desc,
                        x.provname,
                        x.finalized_date,
                        x.log_dtg,
                        x.ssn_last_4,
                        x.ssn_last_2,
                        x.fmpc_id,
                        x.sort_fmpc_id,
                        x.status_group,
                        x.dept_name,
                        x.cmd_name,
                        x.wc_name,
                        x.div_name,
                        x.paygrade_class,
                        x.cmd_dod_uic,
                        x.cmd_assignment,
                        x.visitor_ind ) );
      END LOOP;
             RETURN;
<snip>
  .... give me this for each x ?
 PLS-00306: wrong number or types of arguments in call to
 'PENDING_ISSUES_SET_TYPE' 

Is it possible for a pipelined function to receive data from a unix pipe ?

How much data can a pipelined function hold (10M,20M....)
Thanks

It seems that in all the pipelined function examples I can find, we have to create new types in the database. Is is possible, and if so, could you provide an example, of using the built in XMLType Object to avoid creating new types?

> you can hide the types in package specs and we'll create the types for you -
> they will BE THERE, but you don't have to create them.

You probably meant this (10.2.0.1):

dellera@ORACLE10> create or replace package pkg as
2 type ret is table of varchar2(100);
3 end;
4 /

Package created.

dellera@ORACLE10> create or replace function f return pkg.ret pipelined is begin pipe row ('pippo'); return; end;
2 /

Function created.

dellera@ORACLE10> select * from table(f);

COLUMN_VALUE
------------------------------------
pippo

But:

dellera@ORACLE10> create or replace package pkg
2 as
3 type t_elem is object (x int, y int);
4 type t_elem_array is table of t_elem;
5 end;
6 /

Warning: Package created with compilation errors.

dellera@ORACLE10> show errors;
Errors for PACKAGE PKG:

LINE/COL ERROR
-------- --------------------------------------------------
3/3 PLS-00540: object not supported in this context.

dellera@ORACLE10> create or replace package pkg
2 as
3 type t_elem is record (x int, y int);
4 type t_elem_array is table of t_elem;
5 end;
6 /

Package created.

dellera@ORACLE10> create or replace function f return pkg.t_elem_array pipelined is begin pipe row ( pkg.t_elem (1,1) ); return; end;
2 /

Warning: Function created with compilation errors.

dellera@ORACLE10> show errors
Errors for FUNCTION F:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/55 PL/SQL: Statement ignored
1/66 PLS-00222: no function with name 'T_ELEM' exists in this scope

Or is there a way to create the object in the package spec, too ?

TIA

> (even if we "hid" them in a spec like we can for collections
> referenced in sql - we still have the types - they just have
> ugly mangled names, but they are there)

Ahem, not following you here - pipelined functions apart, do you mean that sql types are "implicitly" created from pl/sql types when using collections ?

Maybe a tiny tiny quick and fast example :)

So, if I understood right, is it then possible to define the collection types required for the pipelined function as a type embedded in a package spec? or does it have to be a true type defined directly in the database by someone with create type authority?

I have demonstrated the use of pipelined functions below.

set serveroutput on size 1000000 format wrapped
drop table t1
/
create table t1 (char_column varchar2(6), number_column number)
/
CREATE OR REPLACE TYPE t1_object_type as object (
char_column varchar2(6), number_column number)
/
CREATE OR REPLACE TYPE t1_table_type AS TABLE of t1_object_type;
/

/* This function tries to read from table T1 and pipe the rows
back to the calling procedure.
*/
create or replace function T1_func
RETURN t1_table_type PIPELINED
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_record t1_object_type := t1_object_type(null,null);
v_rows integer;
begin
select count(*)
into v_rows
from t1;

dbms_output.put_line('beginning function T1_func. Number of T1 rows = '||to_char(v_rows));
commit;
for cur in (select char_column, number_column from t1) loop
v_record.char_column := cur.char_column;
v_record.number_column := cur.number_column;
PIPE ROW(v_record);
end loop;
exception
when others then
dbms_output.put_line ('Error in function T1_func: '||SQLERRM);
end t1_func;
/

/* This procedure inserts data into a table T1 without committing, then tries to insert
more rows through the table function T1_func
*/
create or replace procedure p1
is
v_rows integer;

begin
select count(*)
into v_rows
from t1;
dbms_output.put_line('beginning procedure P1. Number of T1 rows = '||to_char(v_rows));

insert into t1 (char_column, number_column) values ('abc', 123);
insert into t1 (char_column, number_column) values ('def', 456);
insert into t1 (char_column, number_column) values ('ghi', 789);

select count(*)
into v_rows
from t1;
dbms_output.put_line('In procedure P1. After inserts, number of T1 rows = '||to_char(v_rows));
commit;
insert into t1 (char_column, number_column)
(select * from table(t1_func));

select count(*)
into v_rows
from t1;
dbms_output.put_line('In procedure P1. After calling autonomous table function, number of T1 rows = '||to_char(v_rows));

exception
when others then
dbms_output.put_line('Error in procedure P1: '||SQLERRM);
end p1;
/
exec p1;



In my demonstration if I remove PRAGMA_AUTONOMOUS_TRANSACTIONS statement, I get ORA-04091: table SNS.T1 is mutating, trigger/function may not see it.

Why is that?

Also, can I use refcursor in the above example without going for the pipelined functions?

In the above example, I insert 3 rows in the table t1 within the stored procedure. Next I call the function and try to insert the same data again.

Why the rows which I inserted in the procedure is not seen when the call goes to function? I don't want to commit until the function call is successfully executed.

How to achieve this result?

I tried using sys_refcursor as a parameter to function but still I cannot see the rows in my function that I inserted in the procedure.

Any other way to overcome this problem?

Thanks,

do you have an example of how to get rid of autonomous transaction and achieve the results what I want?

I am working on getting the real pl/sql program our company is being using which has the scenario described above.

I will produce it here as soon as I edit it.

Thanks,

Hi Tom,
Thanks for a wonderful discussion on Pipelined functions. It is quite informative.
I have a couple of questions for you, if you don't mind.
1) Firstly, we are using PRO*C version 806 here. And for this version to work with PIPELINED functions, I find that I cannot use collections that are declared within PL/SQL packages. I've had to declare new SQL TYPES. Would you know if this has been changed in later versions? Or if this is the standard behavious for all 'outside' languages seeing as how they would have no idea about a pl/sql package declaration?

2) Secondly, how would you approach the requirement below. WE have a number of PRO*C reports that use basically the same SQL code, but outputs stuff differently. In order to go in for code reuse and modularity as well as maintenance, we have currently taken out the SQL from the reports and used a 'generic' package that contains the SQL code and uses PIPELINED functions to return the resultset. The reasons for this was that the SQL takes in search criteria which we are passing in as parameters to the functions. Also we weren't sure the ref cursor route was right for us, since any change to the 'select' clause would've required a change to all associated reports and we didn't want to do that. Using PIPELINED functions and named column list in the PROC selects, we have gotten around this.
I'm really interested to know how you would do this.

Thanks

In the example above you have said --

host flat scott/tiger emp > /tmp/emp.dat

What is flat ?

Thanks

HI Tom,
Thanks for your response to my question above (reg. Pro*C and pipelined functions).
Reg. the second point, I'm told it is possible that the client may want to add to the column-list in the select clause in a report. And this in-turn would need a change in all the Pro*C reports, to update the struct to hold the info if we used a ref cursor. If we used pipelined functions and used a named list in the report instead, we do away with this and only have to the change the report(s) that are affected.
So right now, for example I've got something like

'exec sql declare cursor mycur as
select field1, field2, field3
from table(mypkg.myfunction(:arg1, :arg2));'

And changing the packaged function to add a new column in doesn't affect my reports.

As for the versions, let's not even go there. I've given up trying to make sense of things like that too:-)

Thanks
Chandini

Hello Tom,
I do not get exception from a pipelined function.
Is this a bug or a feature?
Using 10.2.0.1.0 under RedHat.
Richard

create or replace type t_n as table of number(4)
/
create or replace package p_tst as
function f1 return t_n pipelined;
function f2 return t_n pipelined;
end;
/
show err
create or replace package body p_tst as
function fe return number
as
l_res number;
begin
select 3 into l_res from dual where 1 = 0;
return l_res;
end;
--
function f1 return t_n pipelined
as
begin
pipe row(1);
pipe row(fe);
return;
end;
--
function f2 return t_n pipelined
as
begin
pipe row(1);
pipe row(fe);
return;
exception when others then
raise_application_error(-20001, 'got: ' || sqlerrm);
end;
end;
/
show err
-- does get no exception
select * from table(p_tst.f1);
-- does get exception
select * from table(p_tst.f2);

Hello Tom,

would you give me some tips how to best debug a pipelined function from within say Oracle SQL Developer.

I made a package:

create or replace type t_n as table of number(4)
/
CREATE OR REPLACE
PACKAGE P_TEST_P AS
function f return t_n pipelined;
END P_TEST_P;
/
CREATE OR REPLACE
PACKAGE BODY P_TEST_P AS
function f return t_n pipelined AS
BEGIN
pipe row(1);
return;
END f;
END P_TEST_P;
/

and then tried to debug the function.

SQL Developer generated a small script:

DECLARE
v_Return ONC.T_N;
BEGIN
v_Return := P_TEST_P.F();
-- Modify the code to output the variable
-- DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;

When I try to run the script I get PLS-00653.
I can debug the code at a cost of rewriting the function into a non-pipelined version with all implications.

Is there a better solution for this like smart wrapping or the like?

Yours,
Richard

Hi Tom,
i've created a table function that return a collection,it work s but only when i try to launch it two times.

CREATE OR REPLACE PACKAGE BODY TRA_CAN_PKG AS

FUNCTION get_sel_rif_i1 (tra_can_arg IN tra_can_cur)

RETURN tra_can_tab

PARALLEL_ENABLE (PARTITION tra_can_arg BY ANY)

PIPELINED IS
tra_can_rec sc4file_in%ROWTYPE;

tracciato tra_can_row := tra_can_row('','','','','','','','','','','','','',
'','','','','','','','','','','','','',
'','','','','','','','','','','','','',
'','','','','','','','','','','','','',
'','','','','','','','','','','','','',
'','','','','','','','','','','','','',
'','','','','','','','','','','','','',
'','','','','','','','','','','','','','');

BEGIN

LOOP

FETCH tra_can_arg INTO tra_can_rec;

EXIT WHEN tra_can_arg%NOTFOUND;



IF ((NOT contains_caus(tra_can_rec.cod_mov)) AND contains_sup_ch(tra_can_rec.cod_canale )) THEN

tracciato.cod_luogo_approv:=tra_can_rec.cod_luogo_approv;
tracciato.cod_tipo_circuito:=tra_can_rec.cod_tipo_circuito;
tracciato.cod_merc:=tra_can_rec.cod_merc;
tracciato.cod_art_nazion:=tra_can_rec.cod_art_nazion;
tracciato.cod_soc:=tra_can_rec.cod_soc;
tracciato.cod_divis:='01';
tracciato.cod_mov:=tra_can_rec.cod_mov;
tracciato.data_01:=tra_can_rec.data_01;
tracciato.data_02:=tra_can_rec.data_02;
tracciato.data_03:=tra_can_rec.data_03;
tracciato.data_04:=tra_can_rec.data_04;
tracciato.cod_luogo_approv_sec:=tra_can_rec.cod_luogo_approv_sec;
tracciato.cod_merc_sec:=tra_can_rec.cod_merc_sec;
tracciato.cod_forn_amm:=tra_can_rec.cod_forn_amm;
tracciato.cod_forn_comm_gruppo:=tra_can_rec.cod_forn_comm_gruppo;
tracciato.cod_canale:=tra_can_rec.cod_canale;
tracciato.cod_canale_sec:=tra_can_rec.cod_canale_sec;
tracciato.cod_gsettore_dep:=tra_can_rec.cod_gsettore_dep;
tracciato.cod_gsettore_sec:=tra_can_rec.cod_gsettore_sec;
tracciato.cod_fam_dep:=tra_can_rec.cod_fam_dep;
tracciato.cod_sfam_dep:=tra_can_rec.cod_sfam_dep;
tracciato.cod_segm_dep:=tra_can_rec.cod_segm_dep;
tracciato.cod_gsettore_can:=tra_can_rec.cod_gsettore_can;
tracciato.cod_fam_can:=tra_can_rec.cod_fam_can;
tracciato.cod_sfam_can:=tra_can_rec.cod_sfam_can;
tracciato.cod_segm_can:=tra_can_rec.cod_segm_can;
tracciato.cod_tipo_pv:=tra_can_rec.cod_tipo_pv;
tracciato.cod_posiz_merceol:=tra_can_rec.cod_posiz_merceol;
tracciato.cod_categoria_acquisto:=tra_can_rec.cod_categoria_acquisto;
tracciato.cod_fascia_mercato:=tra_can_rec.cod_fascia_mercato;
tracciato.cod_tipo_stagionalita:=tra_can_rec.cod_tipo_stagionalita;
tracciato.ora_cons_prev:=tra_can_rec.ora_cons_prev;
tracciato.cod_stagionalita:=tra_can_rec.cod_stagionalita;
tracciato.cod_assortimento:=tra_can_rec.cod_assortimento;
tracciato.cod_cat:=tra_can_rec.cod_cat;
tracciato.cod_um_norm:=tra_can_rec.cod_um_norm;
tracciato.qta:=tra_can_rec.qta;
tracciato.volumi:=tra_can_rec.volumi;
tracciato.cod_valuta:=tra_can_rec.cod_valuta;
tracciato.pc:=tra_can_rec.pc;
tracciato.pv:=tra_can_rec.pv;
tracciato.pv_lordo:=tra_can_rec.pv_lordo;
tracciato.stato_ordine:=tra_can_rec.stato_ordine;
tracciato.ora_cons_eff:=tra_can_rec.ora_cons_eff;
tracciato.flag_ventilabile:=tra_can_rec.flag_ventilabile;
tracciato.cod_unita_moviment:=tra_can_rec.cod_unita_moviment;
tracciato.qta_umov:=tra_can_rec.qta_umov;
tracciato.composizione_pallet:=tra_can_rec.composizione_pallet;
tracciato.num_ordine:=tra_can_rec.num_ordine;
tracciato.num_documento:=tra_can_rec.num_documento;
tracciato.cod_iva:=tra_can_rec.cod_iva;
tracciato.perc_iva:=tra_can_rec.perc_iva;
tracciato.perc_scarto:=tra_can_rec.perc_scarto;
tracciato.tara:=tra_can_rec.tara;
tracciato.tipo_pa:=tra_can_rec.tipo_pa;
tracciato.cod_tipo_acq:=tra_can_rec.cod_tipo_acq;
tracciato.cod_tipo_ven:=tra_can_rec.cod_tipo_ven;
tracciato.destinazione_merce:=tra_can_rec.destinazione_merce;
tracciato.flag_edi:=tra_can_rec.flag_edi;
tracciato.flag_gpa:=tra_can_rec.flag_gpa;
tracciato.flag_partita:=tra_can_rec.flag_partita;
tracciato.cod_cliente:=tra_can_rec.cod_cliente;
tracciato.pa_base:=tra_can_rec.pa_base;
tracciato.pa_sconto_001:=tra_can_rec.pa_sconto_001;
tracciato.pa_sconto_002:=tra_can_rec.pa_sconto_002;
tracciato.pa_sconto_003:=tra_can_rec.pa_sconto_003;
tracciato.pa_sconto_004:=tra_can_rec.pa_sconto_004;
tracciato.pa_sconto_005:=tra_can_rec.pa_sconto_005;
tracciato.pa_sconto_006:=tra_can_rec.pa_sconto_006;
tracciato.pa_sconto_007:=tra_can_rec.pa_sconto_007;
tracciato.pa_sconto_008:=tra_can_rec.pa_sconto_008;
tracciato.pa_sconto_009:=tra_can_rec.pa_sconto_009;
tracciato.pa_sconto_010:=tra_can_rec.pa_sconto_010;
tracciato.pa_sconto_011:=tra_can_rec.pa_sconto_011;
tracciato.pa_sconto_012:=tra_can_rec.pa_sconto_012;
tracciato.pa_sconto_013:=tra_can_rec.pa_sconto_013;
tracciato.pa_sconto_014:=tra_can_rec.pa_sconto_014;
tracciato.pa_sconto_015:=tra_can_rec.pa_sconto_015;
tracciato.pa_sconto_016:=tra_can_rec.pa_sconto_016;
tracciato.pa_sconto_017:=tra_can_rec.pa_sconto_017;
tracciato.pa_sconto_018:=tra_can_rec.pa_sconto_018;
tracciato.pa_sconto_019:=tra_can_rec.pa_sconto_019;
tracciato.pa_sconto_020:=tra_can_rec.pa_sconto_020;
tracciato.pa_sconto_021:=tra_can_rec.pa_sconto_021;
tracciato.pa_sconto_022:=tra_can_rec.pa_sconto_022;
tracciato.pa_sconto_023:=tra_can_rec.pa_sconto_023;
tracciato.pa_sconto_024:=tra_can_rec.pa_sconto_024;
tracciato.pa_sconto_025:=tra_can_rec.pa_sconto_025;
tracciato.num_promo_logist:=tra_can_rec.num_promo_logist;
tracciato.progr_riga_ord:=tra_can_rec.progr_riga_ord;
tracciato.progr_riga_bolla:=tra_can_rec.progr_riga_bolla;
tracciato.progr_stralcio:=tra_can_rec.progr_stralcio;
tracciato.lead_time_teorico:=tra_can_rec.lead_time_teorico;
tracciato.peso_merce:=tra_can_rec.peso_merce;
tracciato.volume_merce:=tra_can_rec.volume_merce;
tracciato.sconto_range_min:=tra_can_rec.sconto_range_min;
tracciato.sconto_range_max:=tra_can_rec.sconto_range_max;
tracciato.impegnato_iniz:=tra_can_rec.impegnato_iniz;
tracciato.impegnato_att:=tra_can_rec.impegnato_att;
tracciato.costo_logistico:=tra_can_rec.costo_logistico;
tracciato.cod_collo_misto:=tra_can_rec.cod_collo_misto;
tracciato.cod_itf:=tra_can_rec.cod_itf;
tracciato.filler:=tra_can_rec.filler;
IF is_rif(tra_can_rec.cod_mov) THEN

tracciato.pa_netto:=tra_can_rec.pal;
tracciato.pal:=tra_can_rec.pa_netto;
ELSE
tracciato.pa_netto:=tra_can_rec.pa_netto;
tracciato.pal:=tra_can_rec.pal;
END IF;
PIPE ROW (tracciato);

END IF;

END LOOP;

RETURN;

END;

Thanks for the excellent site.

Please let me know if the Pipelined function would be effective in the below case.

I have a external table like below with around 10Million records. Flag Column stores all the flag code related to the album as a singel record. While I load into the target table each code should go in as seperate record as shown below.

Create table my_music
(album_id NUMBER,
flag VARCHAR2(10)
);

INSERT INTO my_music values(1,'AB');
INSERT INTO my_music values(2,'MKCS');
INSERT INTO my_music values(3,'SEWRT');

SQL> select * from my_music;

ALBUM_ID FLAG
---------- ----------
1 AB
2 MKCS
3 SEWRT

The above records should be loaded into target as

1 A
1 B
2 M
2 K
2 C
2 S
3 s
3 E
3 W
3 R
3 T

Can I use pipelined function here? Will that be the optimal way to load. If yes, can you please show here how to implement it?

Or is there anyother better solution to implement.

Thanks,
Raj

The solution that I had implemented for the above requirement is given below. Will this be an optimal code to load 10 Million records?

create or replace type album_scalar_type as object
(a_id VARCHAR2(10),
flags VARCHAR2(1)
)
/

create or replace type album_table_type as table of album_scalar_type;
/

CREATE OR REPLACE FUNCTION flags_etl( p_cursor IN SYS_REFCURSOR )
RETURN album_table_type PIPELINED AS
v_a_id VARCHAR2(10);
v_a_flag VARCHAR2(10);
v_flg VARCHAR2(1);
BEGIN
LOOP
FETCH p_cursor INTO v_a_id,v_a_flag;
EXIT WHEN (p_cursor%NOTFOUND);
FOR I IN 1..LENGTH(v_a_flag)
LOOP
pipe row( album_scalar_type( v_a_id, SUBSTR(v_a_flag,i,1)) );
END LOOP;
END LOOP;
RETURN;
END;
/

SQL> ed
Wrote file afiedt.buf

1 select a_id, flags from load_amg_cls_calbum
2* where rownum < 5
SQL> /

A_ID FLAGS
---------- ----------
W 1 CDFJ
W 2 CDFJ
W 3 DJC
W 4 DFJ


SELECT *
FROM TABLE(flags_etl(CURSOR( SELECT a_id, flags FROM load_amg_cls_calbum where rownum < 5) ) )

A_ID F
---------- -
W 1 C
W 1 D
W 1 F
W 1 J
W 2 C
W 2 D
W 2 F
W 2 J
W 3 D
W 3 J
W 3 C

A_ID F
---------- -
W 4 D
W 4 F
W 4 J

Thanks!

How is this different from populating Global temporary table and selecting from it. In most cases when I have choose between an record object and global temporary table, I prefer the later.Minimal difference is staying within realm of SQL.Any reason why it should be otherwise

First off, is there such a thing as a non-pipelined table function?

Secondly, I created this test case just to ask about bulk collecting inside a table function. I realize the function goes a long way to do nothing.

SQL> CREATE OR REPLACE TYPE part AS object
  2  (
  3    TABLE_OWNER                            VARCHAR2(30),
  4    TABLE_NAME                             VARCHAR2(30),
  5    PARTITION_NAME                         VARCHAR2(30),
  6    PARTITION_POSITION                     NUMBER
  7  )
  8  ;
  9  /

Type created.

Elapsed: 00:00:00.01
SQL> 
SQL> CREATE OR REPLACE TYPE parttab AS TABLE OF tdinc.part
  2  /

Type created.

Elapsed: 00:00:00.01
SQL> 
SQL> CREATE OR REPLACE FUNCTION get_parts
  2     ( p_owner     VARCHAR2,
  3       p_table     VARCHAR2
  4     )
  5     RETURN parttab
  6     PIPELINED
  7  AS
  8     l_dyn_ddl     LONG;
  9  
 10     t_parttab parttab;
 11  BEGIN
 12  
 13     l_dyn_ddl :=
 14     'SELECT table_owner,'
 15     ||' table_name,'
 16     ||'partition_name,'
 17     ||' partition_position '
 18     || '  FROM all_tab_partitions'
 19     || ' WHERE table_owner = '''
 20     || upper (p_owner)
 21     || ''' AND table_name = '''
 22     || upper (p_table)
 23     || '''';
 24  
 25     EXECUTE IMMEDIATE l_dyn_ddl
 26     bulk collect INTO t_parttab;
 27  
 28     FOR i IN 1 .. t_parttab.count
 29     LOOP
 30  
 31        pipe ROW (t_parttab(i));
 32     END LOOP;
 33  
 34     RETURN;
 35  END get_parts;
 36  /

Function created.

Elapsed: 00:00:00.03
SQL> select * from table(get_parts('BRYSONS','SALES'));
select * from table(get_parts('BRYSONS','SALES'))
                    *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "BRYSONS.GET_PARTS", line 25


Elapsed: 00:00:00.06
SQL> 

While assessing our need for pipelined functions, I wondered if it would be possible to pass hints to Oracle that might make it terminate the function prematurely. Like in the following,

CREATE TYPE t_date_array IS TABLE OF DATE;
/

CREATE FUNCTION generate_dates RETURN t_date_array PIPELINED IS
  v_date DATE := TRUNC( SYSDATE );
BEGIN
  LOOP
    PIPE ROW( v_date );
    v_date := v_date + 1;
  END LOOP;
  RETURN;
END;
/

And then a test:

SQL> SET TIMING ON
SQL> SELECT * FROM TABLE( generate_dates ) WHERE column_value < TRUNC( SYSDATE ) + 7;
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at "HEYTHERETOM.GENERATE_DATES", line 6

no rows selected

Elapsed: 00:00:03.69
SQL>

Now the immediate fix would be to place a realistic limit on the range of dates returned, which would solve both the error (I'm not quite vain enough to assume my procs will still be used in the year 9999) and the amount of time it takes to run. Or I could just query on DUAL with a CONNECT BY combining SYSDATE and LEVEL :). But for illustrative purposes... can I hint to Oracle that generate_dates will always return a date at least as late as the previous date, so it might know to abort the function after so many rows? Something like a check constraint maybe?

Thanks!
-Scott

Hi Tom,

You mentioned earlier that without indexes, Oracle needs to check everything. Since the function generates results in ascending chronological order, there's no need to check any more than 7 rows with the given WHERE clause, but Oracle can't really know this, so it generates all of the results until the year 9999 (assuming we trap and handle the error).

Bottom line, if you have a lineup of people from youngest to oldest, and you want the first 5 youngest people, you don't check all the way to the end of the line. It would be neat if Oracle could be taught to do the same. It's just a thought, I'm probably on the wrong train of thought and should be looking for other methods, but I thought I'd ask :).

Sorry for not including the information...

Actually... I'm using 10.2.0.3

Thanks for your help with this.

The cast doesn't help. I even tried it inside the function, though I knew that wouldn't help either.

SQL> select * from table(cast(get_parts('BRYSONS','SALES') as part));
select * from table(cast(get_parts('BRYSONS','SALES') as part))
                         *
ERROR at line 1:
ORA-22907: invalid CAST to a type that is not a nested table or VARRAY


Elapsed: 00:00:00.07
SQL> show parameter cursor  

NAME                                 | TYPE        | VALUE
------------------------------------ | ----------- | ------------------------------
cursor_sharing                       | string      | EXACT
cursor_space_for_time                | boolean     | FALSE
open_cursors                         | integer     | 300
session_cached_cursors               | integer     | 20
SQL> 

Tom
How does the following work from your original reply:

pipe row( emp_scalar_type( l_rec.empno,
LOWER(l_rec.ename),
l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal,
l_rec.comm, l_rec.deptno ) );

What I mean is is the expression emp_Scalar_type a 'terse' one, is it initialising a Creating and Intialising a Type

2) Question on Pipe Row, obviously, from point one if you have Multiple columns to Output you need to use a Type variable. That is Pipe Row does not always expect its parameter(s) to be a Object Type as in your example

Please see the 'Followup' section of :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425#28960621306776

3) Can I use a Varray as output in Pipe Row.

Thanks

Tom from your previous answer
"yeah, it creates a temporary.

Just like coding:

return 42;

would.
"

Does what really happens is, the Constructor Function of the Object is run, memory gets created for the input function variable values. An instance of the object gets created and the Function returns the pointer to the instance?

As soon as the value is returned, the instance gets destroyed?

declare
   x myType := myType( ...... );
begin
   null;
end;
/

return 42;

Hi Tom,

suppose I have the following table function query:

SELECT * FROM TABLE(test_function(12));

Is there a way that I can create a view based on the above statement which I can pass the function parameter in the where cluse?
something like:

create or replace view test_view as
SELECT b.* FROM TABLE(test_function(a.dept_id))b,
department a
where a.dept_id = b.dept_id

then I'll like to say:

select * from test_view where dept_id = 12


Thank You Very Much

Hello.
And what about scalability of using pipelined functions?
For example my pipelined function that returns collection of objects. Inside function we retrieve data from cursor, puts them into pl/sql collection using bulk fetching. Then processes them somehow in the loop using other queries. And then "pipes" thousands rows.
I guess all returned data will be put into UGA, am I right? What will be happened if 1000 users will execute same queries to this function? When allocated memory will be released? When collection will be released? I didn't found information about using resources and releasing of memory.
Thanks

Hello Mr. Tom.
Some times ago I begun use pipelined functions almost in everywere. I just lovin in it.
And have one question: can I declare and use one pipelined function inside any???

For example:

declare
CURSOR CUR IS
select
uo.object_id,
uo.object_name
from
user_objects uo;

TYPE TYPE_TBL IS TABLE OF CUR%ROWTYPE;
l_res_tbl TYPE_TBL;
-- internal PF ----------------------------
function getInternalDataSet return TYPE_TBL pipelined IS
l_tbl TYPE_TBL;
begin
OPEN CUR;
FETCH CUR BULK COLLECT INTO l_tbl;
FOR i in l_tbl.FIRST..l_tbl.LAST LOOP
pipe row(l_tbl(i));
END LOOP;
CLOSE CUR;
end;
-------------------------------------------
begin
--1
SELECT *
BULK COLLECT INTO l_res_tbl
FROM TABLE(getInternalDataSet);

--2
l_res_tbl:=getInternalDataSet;

end;

--1 and --2 not allowed.
It's suppose that --1 not allowed because SQL engine don't know about function getInternalDataSet at all.

But wy not working second case, I can't understand.
Please show me where I need looking for???

Thanks so much.

Hello Mr. Tom.
Thanks so much for response.
Of course I saw exaggerated, I use it for return data sets to web pages. I think it's good idea incapsulate SQL queries in pipelined functions and call this function from web pages (it's no OLTP system). This approach can help maintain system in the future. And also I remember all time about your relationship to communicate between DBA and developer. I am DB developer and we have web developers, who are clients for me. I want secure my (internal DB) work from those developers. Why not? They can knowlege in ASP, PHP , JS and etc, but not much in Oracle SQL and PL/SQL.
And sometimes they try writes absolutely enificient queries without understanding system. And now I just present documented interface to my set of PF's to those developers and no problems yet.
And also I think this approach move logic near to DB side from WEB site, obviouse it's good.
Can you say me anything about my approach for this area development (just OLAP web).

Thanks again and again.

Thanks. Of course I agree and do it, I am about "Most everything you could think to do - can and should be done in SQL ".
I just invoke "simple" SQL queries (regular SQL queries) in PF.
Problems that I have set of SQL queries (only for present data in OLAP web system) where are each about 100 lines.
But busenes rules can change often, in this case I need so fast make this changes in every SQL.
I need take this SQL queries (no one) from web developers,
make change, test it and return for web developers.
They (web developers) can try to make changes without me and born logical errors or wrong efficiecy.
With PF I save (try save) me, queries and web developers from this errors.
May be best way is using functions that return ref cursors??? I right.
Sorry, I don't done it before, but suppouse it's not difficult.
Can you say me -"Yes it's good way to invoke SQL queries with functions that return ref cursors, for present data sets on web"?????
Sorry for I take your time and thanks more and more.

declare 
CURSOR CUR IS 
 select 
       uo.object_id,
       uo.object_name
   from
       user_objects uo;

TYPE TYPE_TBL IS TABLE OF CUR%ROWTYPE;
l_res_tbl TYPE_TBL;
-- internal PF ----------------------------
function getInternalDataSet return TYPE_TBL pipelined IS
 l_tbl TYPE_TBL;
 begin
  OPEN CUR; 
  FETCH CUR BULK COLLECT INTO l_tbl;
   FOR i in l_tbl.FIRST..l_tbl.LAST LOOP
    pipe row(l_tbl(i));
   END LOOP;
  CLOSE CUR;
 end; 

function getInternalDataSet return sys_refcursor 
IS
   l_cur sys_refcursor;
begin
  OPEN l_cur for 
   select uo.object_id, uo.object_name
     from user_objects uo;

  return l_cur;
end;

Hello Mr. Tom.
I agree with and yeasterday begin my research in this field.
I try rewrite my Pipelined functions to functions that return ref cursors. We use PHP and I don't have strong PHP knowlege (just very beginner in PHP). I asked ower developers about there experiense in work with functions that return ref cursors. They doesn't know about it. It means that I need do it before and explain how it works.
I use Oracle10g, PHP5 and ZendStudio. I try search examples in internat, Oracle site and book "The Underground PHP
and Oracle® Manual". It's work well just before fetching from ref cursor. I found too small in internet about this question. Examples almost absents at all. Why? It's looks like nobody use this approach. Why, if it's recomended by you and any profs(I read also some materials in Russian "Using link to ref cursor serve as a criterion for Oracle professional"
1part- http://www.citforum.ru/database/oracle/cursor/
2part- http://www.citforum.ru/database/oracle/cursor1/ )?

Today I am going to continue my work in this and response about my success.
Of course I have your books, but not readed fully yet.

And one questions in this theme:
I have PFs looks like :
function func_name ...
cursor c1 is ...
cursor c2 is ...
cursor c3 is ...
begin

<here I have good readable logic, where I open cursors, fetch bulk collection and any. Code in this part looks short, readable and maintable.>

end;

In this approach I shure that I use bind variables when open cursors.
How can I change code structure in begin .. end part with saving readable.
If I will use your example:
Code can looks next:

function func_name ...
begin

here long code with too much
(OPEN C1 FOR ...Test SQL queries...)

end;
And I can't use USING when write OPEN C1 FOR... .
I need be sure that I use BV's when OPEN C1 FOR ...
where C1 ref cursor.

Thanks so much for your time that I take.
And sorry for my English I need improve it too.

function func_name ...
begin
<b>
here long code with too much</b>
(OPEN C1 FOR ...Test SQL queries...)

end;

"The Underground PHP and Oracle® Manual" release 1.4 gives a simple but full example of accessing a REF CURSOR on p. 143.

This example uses the PHP Oracle extension called OCI8. You really need to use OCI8 because the other extension, called PDO, is not mature.

I strongly suggest using PHP 5.2.5, because earlier versions have OCI8 drivers with important bugs. In fact, the current version of "Zend Core for Oracle" has an out-of-date OCI8 driver. Thanks to Christopher Jones of Oracle, Zend is in the process of fixing this.

As Tom has said elsewhere, using a REF CURSOR means you will parse at every execution. If you set the session_cached_cursor parameter high enough, the parse will be a "softer soft parse" that reduces the cost of parsing.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082#2595492927824

Hello Mr. Tom.
Thanks to you and any who response me.
Yes, I found way to proeccess ref cursor in PHP.
But in this project I need stay as is. Of course in the next project I will begin from functions that return ref cursors.
Yes. Now it's a lot of not only PL/SQL code and PHP.

Thanks.

Hi Tom,

What happens internally when you use pipelined functions? They seem to be the only way to use stored procedures on Ruby on Rails ( http://wiki.rubyonrails.org/rails/pages/OracleStoredProceduresAsDataSource ). I'm wondering because there's a popular RoR plugin called will_paginate which does a lot of pagination for you if you just give it an SQL query. If you give it something like:

select col1, col2, col3 from table(function(param1,param2))

it will automatically form:

select count(*) from (select col1, col2, col3 from table(function(param1,param2)))

to count the results. I know you frown on applications that count the number of results but bear with me for a moment - does the above result in the function needlessly retrieving data for col1, col2, etc when all that is needed is the number of results or is there (hopefully) some sophistication/intelligence in Oracle that prevents this from happening?

The RoR plugin also generates something like the ff to get for example results 16-30:

select * from (select raw_sql_.*, rownum raw_rnum_ from (select col1, col2, col3 from table(function(param1,param2))) raw_sql_ where rownum <= 30) where raw_rnum_ > 15

Again, does this mean the function will still read all the data for results 1-15 first? Am I better off not using the plugin?

Thank you very much.

Johann

Thanks for response Tom.

So the function will still read all data to form records 1-15, though they won't be sent back to the client. It looks like this will really be bad if say, I get rows 901-915 -- the function will still read all the data to form records 1-900 only to be filtered out. Is there any way to show statistics to demonstrate how bad this can get?

It sure looks like I'm better off feeding the plugin with SQL equivalent to the output of the stored function so that instead of it generating

select * from
(select raw_sql_.*, rownum raw_rnum_ from
(select col1, col2, col3 from
table(function(param1,param2))) raw_sql_
where rownum <= 901)
where raw_rnum_ > 915

it generates:

select * from
(select raw_sql_.*, rownum raw_rnum_ from
(select col1, col2, col3 from
<place logic equivalent to stored function here>)) raw_sql_
where rownum <= 901)
where raw_rnum_ > 915

This does mean Oracle won't have to read all that data for rows 1-900 in the non-stored function version right?

Or maybe I should drop using that plugin altogether and write my own. Because not using a stored function means I have to move the logic that dynamically forms the SQL based on the available parameters (following http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279 ) to the RoR code. I'm thinking a stored function that includes what M to N records to retrieve in the parameters would be better.

Your thoughts on this would be greatly appreciated. Thanks!

Johann

Hi Tom,

Of course, I don't expect sane users to click "next page" some 600 times to get the 901th row. But then a web crawler might. And even if I'm just at page 2 the pipelined function will still needlessly read data for all the records of page 1. What if the site gets popular enough and there are 1000 users at a time who go to page 2. It looks like it's going to be wasteful and if so I need to be able to show how problematic it would be. I'm just concerned of the possible resource hog and effect on the performance of the application to other users when such things happen.

Or am I worrying about optimization too much -- is it acceptable/practice to just "make room" in the hardware for such scenarios?

Thanks

Johann

Google stops at 1000 "results", no matter how many results per page you want. http://www.google.fr/search?hl=en&q=tom+kyte&start=1000&sa=N returns "Sorry, Google does not serve more than 1000 results for any query."

If you want to tune, I like your idea of "a stored function that includes what M to N records to retrieve in the parameters". That way you can tune the underlying SQL and limit the last possible row to 1000 or whatever number you want.

If you think the underlying SQL needs to be tuned, use an index to satisfy the WHERE and the ORDER BY clauses (you do need to ORDER the rows before pulling out the lines you want). Look here (read down a little to see where Tom talks about indexes) : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:76812348057

Thanks for all the suggestions, Tom and Stew!

Hi,

Which one will be faster -

1) FOR vrt_row IN ( SELECT * FROM tbl )
LOOP
PIPE ROW (vrt_row);
END LOOP;

OR

1) Select * bulk collect into vrt_row from tbl;

I'm trying to keep either of them in a function and then use it as table function (select * from function) so based on the performance i can use it.

Can you please tell which one will be faster and why?

Thanks
Pranav.

Hi,

I'm just rephrasing my question -

Purpose - To select data from a Table (no transformation logic is needed)

Which one will be faster -

1)
vrt_row tbl%rowtype;
begin
FOR vrt_row IN ( SELECT * FROM tbl )
LOOP
PIPE ROW (vrt_row);
END LOOP;
end;

OR

1)
vrt_row tbl%rowtype;
Begin
Select * bulk collect into vrt_row from tbl;
End;

OR

1) Creation of View - Select * from tbl;
And let users use the view rather than the Table functions (no creation of object is required here)

Can you please tell which one will be faster and why?

Appreciate your time.

Thanks
Pranav.

Thanks for the quick response.

I don't want the Java app to include table names, but views will work. So i guess comparing to the use of Table functions or Bulk collect if it's just a single table we can use views.

How about if the App is expecting an Object in return. Which will be good (Pipe row or Bulk collect, assume no transformation logic here just an object to be filled and returned)?

Thanks again
Pranav.

create or replace procedure p( ...inputs....., p_cursor in out sys_refcursor )
as
begin
  open p_cursor for
  select .......;
end;

Excellent original question. (So far) I've only found one use for pipelined functions.

My ODBC-driver for DB2 seems to be limited to strings of no more than 255 characters. But some of the fields I needed were longer than 255 characters.

To pull a 2000 character-wide field through ODBC, I had to ask the source DBMS (DB2, in my case) to split the field into parts, then ask Oracle to reassemble it into one field. To make DB2 do its part, I had to invoke DBMS_HS_PASSTHROUGH procedures. Pipelined functions let me treat the results like a standard query.

http://it.toolbox.com/blogs/data-ruminations/stick-odbc-in-your-pipelined-function-and-view-it-27371

In all above examples, It seems that you HAD to create the physical sql type on the database.

in 10g 3yrs. after the initial question, Is it possible to do this in pure pl/sql ? or we are still struggling ?

Tom, Thanks for a wonderful site as always.
I have written a pipelined function to return rows after some processing. I have a bit of code that needs to be repeated so I thought I would put it in a local sub-program to do this. However I can't get the function to compile now.

Basically I am doing something like
Create or replace function xyz
return blah_type pipelined
as
begin
---some local variables

For rec in (select * from dual) Loop
If <some condn satisfied> then
do_something;
-- do some processing here
pipe row (blah_type);
end if;
end loop;

do_something;

end xyz;




The do_something part is repeated in two sections of the report. And it includes a call to pipe row as well, since I need to put out some summary data depending on some conditions. Ideally I'd like to put the whole do_something bit into a sub-program within the function but PLSQL doesn't like it. I tried creating a local function that is also pipelined, but it didn't like any way I tried to call it.

Any ideas how I would do this? Or if this is even possible. Do I just get on and repeat my code twice?

Thank you

CREATE OR REPLACE TYPE mytabletyp IS TABLE OF NUMBER;

CREATE OR REPLACE FUNCTION non_pipe_fnc(p_limit IN NUMBER) IS
RETURN mytabletyp
AS
 mycol mytabletyp := mytabletyp();
BEGIN
 FOR i IN 1..p_limit
 LOOP
  mycol.extend;
  mycol(mycol.count) := i;
 END LOOP;
 RETURN mycol; 
END non_pipe_fnc;

CREATE OR REPLACE FUNCTION pipe_func (p_limit IN NUMBER) 
RETURN mytabletyp PIPELINED
AS
 mycol mytabletyp := mytabletyp();
BEGIN
 FOR i IN 1..p_limit
 LOOP
  pipe row(i);
 END LOOP;
 
 RETURN;
END pipe_func; 

********************************************************************************

SELECT * 
FROM
 TABLE(non_pipe_fnc(100000))


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0         36          0           0
Execute      1      0.06       0.07          0          0          0           0
Fetch      668      0.01       0.06          0          0          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      670      0.07       0.14          0         36          0      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     668        0.00          0.00
  SQL*Net message from client                   668        0.44         19.01
********************************************************************************


********************************************************************************

SELECT * 
FROM
 TABLE(pipe_func(100000))


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0         36          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      668      0.09       0.12          0          0          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      670      0.09       0.13          0         36          0      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100  

Rows     Row Source Operation
-------  ---------------------------------------------------
 100000  COLLECTION ITERATOR PICKLER FETCH PIPE_FUNC (cr=0 pr=0 pw=0 time=100092 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     668        0.00          0.00
  SQL*Net message from client                   668        0.06         10.82
********************************************************************************

Tom,
<Quote from Oracle product documentation>
With non-pipelined table functions, the entire collection returned by a table function must be constructed and returned to the server before the query can return a single result row.
Pipelining enables rows to be returned iteratively, as they are produced.
This also reduces the memory that a table function requires, as the object cache does not need to materialize the entire collection.
</quote>

Questions.
1) I could not see any major difference interms of IO or Elapsed/CPU time between a Non Pipelined Table function & Pipelined Table function.
2) Why Execution Plan is missing in TKPROF output for Non Pipelined Table function ?
3) How can i compare the Non Pipelined Table function & Pipelined Table function interms of Performance?

Please correct me if am doing wrong.

Thank You,
Rajesh.

CREATE TYPE all_obj_type IS OBJECT
(
  OWNER           VARCHAR2(30),
  OBJECT_NAME     VARCHAR2(30),
  SUBOBJECT_NAME VARCHAR2(30),
  OBJECT_ID       NUMBER,
  DATA_OBJECT_ID NUMBER,
  OBJECT_TYPE     VARCHAR2(19),
  CREATED         DATE,
  LAST_DDL_TIME   DATE
);  

CREATE OR REPLACE TYPE all_obj_tt IS TABLE OF all_obj_type;

CREATE OR REPLACE FUNCTION non_pipe_fnc 
RETURN all_obj_tt IS
 all_obj  all_obj_tt := all_obj_tt();
BEGIN
 FOR r IN (SELECT * FROM ALL_OBJECTS)
 LOOP
  all_obj.EXTEND;     
  all_obj(all_obj.COUNT) := all_obj_type(r.OWNER,r.OBJECT_NAME,r.SUBOBJECT_NAME,r.OBJECT_ID,r.DATA_OBJECT_ID,r.OBJECT_TYPE,r.CREATED,r.LAST_DDL_TIME); 
 END LOOP;
 RETURN all_obj;
END non_pipe_fnc;

CREATE OR REPLACE FUNCTION pipe_fnc
RETURN all_obj_tt
PIPELINED IS 
 all_obj  all_obj_tt := all_obj_tt();
BEGIN
 FOR r IN (SELECT * FROM ALL_OBJECTS)
 LOOP
  all_obj.EXTEND;  
  PIPE ROW(all_obj_type(r.OWNER,r.OBJECT_NAME,r.SUBOBJECT_NAME,r.OBJECT_ID,r.DATA_OBJECT_ID,r.OBJECT_TYPE,r.CREATED,r.LAST_DDL_TIME));
 END LOOP;
 RETURN;
END pipe_fnc; 

********************************************************************************

select * 
from
 table (non_pipe_fnc)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0        153          0           0
Execute      1      0.34       0.32          0          0          0           0
Fetch      274      0.31       0.19          0          0          0       40876
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      276      0.67       0.52          0        153          0       40876

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
  40876  COLLECTION ITERATOR PICKLER FETCH NON_PIPE_FNC (cr=85648 pr=0 pw=0 time=4246240 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     274        0.00          0.00
  SQL*Net message from client                   274        0.73         11.51
  SQL*Net more data to client                   627        0.00          0.00
********************************************************************************

SELECT * 
FROM
 ALL_OBJECTS


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      409      0.78       0.81          0      68150          0       40876
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      411      0.78       0.81          0      68150          0       40876

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID SUM$ (cr=0 pr=0 pw=0 time=0 us)
      0   INDEX UNIQUE SCAN I_SUM$_1 (cr=0 pr=0 pw=0 time=0 us)(object id 398)
  40876  FILTER  (cr=85623 pr=0 pw=0 time=496535 us)
  53184   HASH JOIN  (cr=1058 pr=0 pw=0 time=213592 us)
     68    TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=105 us)
  53184    TABLE ACCESS FULL OBJ$ (cr=1052 pr=0 pw=0 time=53490 us)
   2125   TABLE ACCESS BY INDEX ROWID IND$ (cr=5510 pr=0 pw=0 time=25080 us)
   2730    INDEX UNIQUE SCAN I_IND1 (cr=2773 pr=0 pw=0 time=10165 us)(object id 39)
  20766   NESTED LOOPS  (cr=61582 pr=0 pw=0 time=332449 us)
  23154    INDEX RANGE SCAN I_OBJAUTH1 (cr=61582 pr=0 pw=0 time=165445 us)(object id 103)
  20766    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=87148 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=18 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=8 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=10 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=10 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=11 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=9 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=10 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=20 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=11 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=16 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=11 us)
     26   VIEW  (cr=0 pr=0 pw=0 time=165 us)
     26    FAST DUAL  (cr=0 pr=0 pw=0 time=65 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=10 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=17 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=9 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=12 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=18 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=9 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=13 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)

--------------------------------------------------------------------------------

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          3          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       15.39         15.39
********************************************************************************

select * 
from
 table (pipe_fnc)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0         96          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      274      2.85       3.70          0      82388          0       40876
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      276      2.87       3.70          0      82484          0       40876

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Rows     Row Source Operation
-------  ---------------------------------------------------
  40876  COLLECTION ITERATOR PICKLER FETCH PIPE_FNC (cr=85644 pr=0 pw=0 time=2925569 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                     274        0.00          0.00
  SQL*Net message from client                   274        0.62         44.10
  SQL*Net more data to client                   627        0.00          0.00
********************************************************************************

SELECT * 
FROM
 ALL_OBJECTS


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      409      0.82       0.83          0      68150          0       40876
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      411      0.82       0.83          0      68150          0       40876

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID SUM$ (cr=0 pr=0 pw=0 time=0 us)
      0   INDEX UNIQUE SCAN I_SUM$_1 (cr=0 pr=0 pw=0 time=0 us)(object id 398)
  40876  FILTER  (cr=85620 pr=0 pw=0 time=578217 us)
  53184   HASH JOIN  (cr=1058 pr=0 pw=0 time=213788 us)
     68    TABLE ACCESS FULL USER$ (cr=6 pr=0 pw=0 time=106 us)
  53184    TABLE ACCESS FULL OBJ$ (cr=1052 pr=0 pw=0 time=53495 us)
   2125   TABLE ACCESS BY INDEX ROWID IND$ (cr=5510 pr=0 pw=0 time=26170 us)
   2730    INDEX UNIQUE SCAN I_IND1 (cr=2773 pr=0 pw=0 time=10540 us)(object id 39)
  20766   NESTED LOOPS  (cr=61582 pr=0 pw=0 time=338453 us)
  23154    INDEX RANGE SCAN I_OBJAUTH1 (cr=61582 pr=0 pw=0 time=171441 us)(object id 103)
  20766    FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=89493 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=18 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=9 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=11 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=11 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=9 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=12 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=18 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=19 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=22 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=17 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=14 us)
     26   VIEW  (cr=0 pr=0 pw=0 time=179 us)
     26    FAST DUAL  (cr=0 pr=0 pw=0 time=61 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=20 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=17 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=9 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=17 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=22 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=9 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=15 us)
      0   FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=0 us)

--------------------------------------------------------------------------------

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          3          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          3          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       10.17         10.17
********************************************************************************

Tom,
Pipelined Function took total of 3.70 secs, while Non_Pipelined_Function took 0.52 sec
Is the delay in pipelined function is due to " Pipelining enables rows to be returned iteratively "?

Thanks,
Rajesh.

ops$tkyte%ORA11GR2> CREATE OR REPLACE FUNCTION non_pipe_fnc
  2  RETURN all_obj_tt IS
  3      all_obj  all_obj_tt := all_obj_tt();
  4  BEGIN
  5      FOR r IN (SELECT * FROM ALL_OBJECTS)
  6      LOOP
  7          all_obj.EXTEND;
  8          all_obj(all_obj.COUNT) := all_obj_type(r.OWNER,r.OBJECT_NAME,r.SUBOBJECT_NAME,r.OBJECT_ID,r.DATA_OBJECT_ID ,r.OBJECT_TYPE,r.CREATED,r.LAST_DDL_TIME);
  9      END LOOP;
 10      RETURN all_obj;
 11  END non_pipe_fnc;
 12  /
Function created.

ops$tkyte%ORA11GR2> CREATE OR REPLACE FUNCTION pipe_fnc
  2  RETURN all_obj_tt
  3  PIPELINED IS
  4  BEGIN
  5      FOR r IN (SELECT * FROM ALL_OBJECTS)
  6      LOOP
  7          PIPE ROW(all_obj_type(r.OWNER,r.OBJECT_NAME,r.SUBOBJECT_NAME,r.OBJECT_ID,r.DATA_OBJECT_ID,r.OBJECT_TYPE,r.CREATED,r.LAST_DDL_TIME));
  8      END LOOP;
  9      RETURN;
 10  END pipe_fnc;
 11  /
Function created.

ops$tkyte%ORA11GR2> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly
ops$tkyte%ORA11GR2> select * from table(NON_PIPE_FNC);
66348 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1517095174

--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| NON_PIPE_FNC |  8168 | 16336 |    29   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        803  recursive calls
          0  db block gets
      47187  consistent gets
          0  physical reads
          0  redo size
    3078130  bytes sent via SQL*Net to client
      49072  bytes received via SQL*Net from client
       4425  SQL*Net roundtrips to/from client
       1491  sorts (memory)
          0  sorts (disk)
      66348  rows processed

ops$tkyte%ORA11GR2> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from table(PIPE_FNC);

66348 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2394767287

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PIPE_FNC |  8168 | 16336 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        768  recursive calls
          0  db block gets
      47142  consistent gets
          0  physical reads
          0  redo size
    3078130  bytes sent via SQL*Net to client
      49072  bytes received via SQL*Net from client
       4425  SQL*Net roundtrips to/from client
       1490  sorts (memory)
          0  sorts (disk)
      66348  rows processed

ops$tkyte%ORA11GR2> exec runStats_pkg.rs_stop;
Run1 ran in 1174 cpu hsecs
Run2 ran in 1139 cpu hsecs
run 1 ran in 103.07% of the time

Name                                  Run1        Run2        Diff
...
LATCH.JS slv state obj latch        43,452           1     -43,451
STAT...session uga memory max      196,560           0    -196,560
STAT...session pga memory          327,680           0    -327,680

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
381,721     336,382     -45,339    113.48%

PL/SQL procedure successfully completed.

Hello Tom,

Nice example

why the latch count more for nonpipeline funstion?

Is it not that array is in PGA why oracle need to latch?

Thanks

Thanks for your reply


.....It might be populated in your UGA, but it goes out to the greater world when you return it.


Could please elobrate more on your above statement


Many thanks

Hi,

I have hit a wall with something I am trying out. I have a recursive query using 11g R2's latest "subquery factoring" clause, but there are all the restrictions of not being able to use distinct, or group by or using the query_name in a subquery, keeping all that in mind, I have a function that returns the child nodes to traverse in a string ex: 1,4,6.
Ex: get_child_nodes( ParentNode )

Is there a way I can write the SQL of the recursive block such that it doesn't use the query in a subquery?

I was thinking something along the lines of

select get_child_nodes( a.Node )
from query_name a

But I would need something in the select clause that can split the string and maybe using CAST and TABLE returns me the next child nodes as rows now! If I did the above where I use get_child_nodes and query_name in a FROM section then it would violate the recursive subquery factoring restrictions.

If this is not possible can you please suggest me a nice way to implement recursion (SQL or PL/SQL...pref SQL :) ) when the recursive block that decides on the child nodes can only be decided by a complex query involving group bys, inline views, or analytical functions etc?
Or can a group by, having clause be completely rewritten using analytical functions? I notice that partition over ( ) doesn't suppress duplicates like group by does.

Your advice would be most appreciated.


Regards,
Sven

Hi Tom, 

I have taken this part of the text from the above link. Even I am facing the same issue. As per your suggestion below, I used the parttab type instead of the type part.

SQL Query that I executed:
select * from table(cast(get_parts('BRYSONS','SALES')as parttab));

It is returning the below error:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "QBANAPPS.GET_PARTS", line 25
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

Kindly provide your suggestion on this.
Thanks in advnace.

-------Part taken from the above link-----------

Thanks for your help with this. 

The cast doesn't help. I even tried it inside the function, though I knew that wouldn't help either. 

SQL> select * from table(cast(get_parts('BRYSONS','SALES') as part));
select * from table(cast(get_parts('BRYSONS','SALES') as part))
                         *
ERROR at line 1:
ORA-22907: invalid CAST to a type that is not a nested table or VARRAY


Elapsed: 00:00:00.07
SQL> show parameter cursor  

NAME                                 | TYPE        | VALUE
------------------------------------ | ----------- | ------------------------------
cursor_sharing                       | string      | EXACT
cursor_space_for_time                | boolean     | FALSE
open_cursors                         | integer     | 300
session_cached_cursors               | integer     | 20
SQL> 





Followup   April 18, 2007 - 9am Central time zone:

oh, the function returns parttab, a table of parts, use the parttab type. 

Hi Tom,
first of all, many thanks for driving this forum. It's provided many answers to me and helped a lot many times.
Now, what's my point.
I observe strange behavior regarding pipelined function, at least to me. I have a package as follows:

CREATE OR REPLACE PACKAGE BODY pck_load_empdata
IS
  FUNCTION fnc_parse_xml(ax_xml_csv IN XMLTYPE) RETURN bsp.t_ntt_stg_employee
  PIPELINED IS
  BEGIN
    --parse the XML/CSV
    --bsp.t_ntt_stg_employee is TABLE OF TYPE that corresponds to bsp.stg_employee table structure
  END;    
    
  PROCEDURE prc_populate_stg (ax_csv IN xmltype)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO bsp.stg_employee
    SELECT *
      FROM TABLE(fnc_parse_xml(ax_csv));
    COMMIT;
  END;
  
  PROCEDURE prc_merge_emps
  IS
  BEGIN
    --do something with the loaded data and data in DB
  END;
  
  PROCEDURE prc_load_emp_batch(ax_csv IN xmltype, ax_csv OUT xmltype)
  IS
  BEGIN
    prc_populate_stg(ax_msg_in);
    prc_merge_emps;
  END;
END;

When debugging I observe the merge procedure is skipped by the debugger, i.e. it doesn't want to enter it, nor it enters the COMMIT line. Simply it jumps directly at the end of the package.
When, however, prc_populate_stg does insert append, it strangely enters the commit line and executes the merge procedure as expected.
Logging is always turned on on the table bsp.stg_employee.
Although the issue seems to get solved by this, but I'd like to understand why this happens.
Do the pipelined table functions always need to do append and I just overlooked that in doc?
Am I doing something wrong or understand wrong pipelined functions? or is this correct behavior or .. any other conclusion ?

Original code runs at Oracle 10.2.0.5, AIX

Thank you very much in advance.

Hi,

thank you for quick response. Later, once they are available to me I'll try with other version and platform if it behaves the same way.

@Daniel from Czech Republic

First could you please give your oracle release. Very often it matters in such cases

Second, have you shown here the exact code you are using? particularly, have you exceptions blocs in your code that you didn't show here?

Third, is your pipelined function selecting from the table bsp.stg_employee ?
if so then please pay attention to the situation I have presented in the following article

http://hourim.wordpress.com/2012/02/09/materialized-hint-and-ora-04091-error/

@Mohamed Houri

Hi Mohamed,
First, Oracle release I'm using was mentioned in my original question post, it is 10.2.0.5 IBM/AIX.
Second, I can't show the exact code and in this processing stage, there are no exception blocks.
Third, no, my pipelined function is NOT selecting from the table is is inserting in. I'm aware of the mutating table issue in such a case. the function just parses given XML/CSV and pipes out the objects set with CSV fields.

As I already said, I'll try to test the same (or very similar) code on different Oracle version/platform. Might be it's a version-platform specific issue.

In the begining of this post you mentioned the following example, i understood the entire example except a few lines.

i didn't understand this i highlighted in bold what does this mean ? insert data into emp_scalar_type ???

<b>pipe row( emp_scalar_type</b>( l_rec.empno,
 17                            LOWER(l_rec.ename),
 18                            l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal,
 19                                    l_rec.comm, l_rec.deptno ) );

and why did you mention TABLE here ? 

 select empno, ename
  2    <b>from TABLE</b>(emp_etl(cursor( select * from external_table ) ) )
  3   where ename like '%a%';

In your original example (very beginning), you mentioned that you could "log bad rows elsewhere". Can you have an insert statement in a function that gets called in a SQL statement? WOuldn't you get this error?

ORA-14551: cannot perform a DML operation inside a query


demo@ORA9IR2> create or replace
2 function emp_etl( p_cursor in sys_refcursor )
3 return emp_table_type
4 PIPELINED
5 as
6 l_rec external_table%rowtype;
7 begin
8 loop
9 fetch p_cursor into l_rec;
10 exit when (p_cursor%notfound);
11
12 -- validation routine
13 -- log bad rows elsewhere
14 -- lookup some value
15 -- perform conversion
16 pipe row( emp_scalar_type( l_rec.empno,
17 LOWER(l_rec.ename),
18 l_rec.job, l_rec.mgr, l_rec.hiredate, l_rec.sal,
19 l_rec.comm, l_rec.deptno ) );
20 end loop;
21
22 return;
23 end;
24 /

Hello Tom,
Will there be any performance impact on joining a table with another built on a piped-line function using table()?

--Object type
CREATE OR REPLACE TYPE ty_as_data AS OBJECT
(
as_id NUMBER(18),
description VARCHAR2(4000),
rating VARCHAR2(20),
last_admin_date DATE,
exception_date DATE,
rat_type VARCHAR2(20) );

--Table Type
CREATE OR REPLACE TYPE tab_as_data IS TABLE OF ty_as_data;


--Function
create or replace FUNCTION f_as_data
RETURN tab_as_data PIPELINED
AS
BEGIN
FOR i IN (SELECT as_id,
description,
rating,
last_admin_date,
exception_date,
rat_type
FROM table1,
table2,
table3
WHERE <<complex join>>)
LOOP
PIPE ROW(ty_as_data(i.as_id, i.description,
i.rating, i.last_admin_date,
i.exception_date, i.rat_type));
END LOOP;
RETURN;
END f_as_data;


--Usage
SELECT a.as_id, a.description
FROM TABLE(secid_fn_anlytc_sec_data_apr()) a,
table100
where a.as_id = table100.as_id;

I know this is an very vague question and without data/indexes you won't be able to answer but was curious are there any performance impact on using the above query?

Table100 - around 10,000+ rows and are indexed.
TABLE(secid_fn_anlytc_sec_data_apr()) - returns around 35,000+ rows

Is there a better way of writing the above query?

Yes, the FOR loop in function will be changed to bulk collect.

Hi Tom,

I have question on one of the previous example you have mentioned.

We have read about the benefit of pipelined table function against normal table functions. But when we have a option of directly selecting from table should we opt for pipelined table function?

As you have always said try to do it in SQL if possible, else use PL/SQL. So, in the below example of selecting data from ALL_OBJECTS table, should we use pipelined function or we should directly select from ALL_OBJECTS table?

CREATE TYPE all_obj_type IS OBJECT
(
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE
);
/

CREATE OR REPLACE TYPE all_obj_tt IS TABLE OF all_obj_type;
/

CREATE OR REPLACE FUNCTION pipe_fnc
RETURN all_obj_tt
PIPELINED IS
BEGIN
FOR r IN (SELECT * FROM ALL_OBJECTS)
LOOP
PIPE ROW(all_obj_type(r.OWNER,r.OBJECT_NAME,r.SUBOBJECT_NAME,r.OBJECT_ID,r.DATA_OBJECT_ID,r.OBJECT_TYPE,r.CREATED,r.LAST_DDL_TIME));
END LOOP;
RETURN;
END pipe_fnc;
/

SELECT * FROM table(pipe_fnc);
/

SELECT * FROM ALL_OBJECTS;
/

Team,

we got a Polymorphic table function implemented like this and it works fine

demo@XEPDB1>  create or replace package pkg_export_to_csv
  2    as
  3       function describe( p_tab in out dbms_tf.table_t )
  4       return dbms_tf.describe_t;
  5
  6       procedure fetch_rows ;
  7    end;
  8    /

Package created.

demo@XEPDB1>  create or replace package body pkg_export_to_csv
  2     as
  3        function describe( p_tab in out dbms_tf.table_t )
  4        return dbms_tf.describe_t
  5        as
  6                l_new_cols dbms_tf.COLUMNS_NEW_T;
  7        begin
  8                for i in 1..p_tab.column.count
  9                loop
 10                                p_tab.column(i).pass_through := false;
 11                                p_tab.column(i).for_read := true;
 12                end loop;
 13
 14                l_new_cols( l_new_cols.count+1 ) := dbms_tf.COLUMN_METADATA_T( name => 'csv_cols' );
 15                return dbms_tf.describe_t( new_columns => l_new_cols );
 16        end;
 17
 18        procedure fetch_rows as
 19                l_env dbms_tf.env_t ;
 20                l_rowset dbms_tf.row_set_t;
 21                l_rowcnt number;
 22                l_colcnt number;
 23                l_piece long;
 24                l_rows number := 0 ;
 25                l_value dbms_tf.tab_varchar2_t ;
 26        begin
 27                l_env := dbms_tf.get_env;
 28                dbms_tf.get_row_set( l_rowset,l_rowcnt,l_colcnt );
 29
 30                for r in 1..l_rowcnt
 31                loop
 32                        dbms_tf.xstore_get('X'||r,l_rows);
 33                        l_piece := null;
 34                        for c in 1..l_colcnt
 35                        loop
 36                                l_piece := l_piece ||','||
 37                                        dbms_tf.col_to_char( l_rowset(c),r );
 38                        end loop;
 39                        l_value(r) := trim(',' from l_piece);
 40                        dbms_tf.xstore_set('X'||r,l_rows+l_rowcnt);
 41                end loop;
 42
 43                if l_colcnt > 0 then
 44                        dbms_tf.put_col(1,l_value);
 45                end if;
 46        end;
 47     end;
 48     /

Package body created.

demo@XEPDB1> create or replace function export_to_csv(
  2    p_tab in table )
  3  return
  4  table pipelined
  5  table polymorphic
  6  using pkg_export_to_csv;
  7  /

Function created.

demo@XEPDB1> select * from export_to_csv(dept);

CSV_COLS
------------------------------
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

demo@XEPDB1>

but now we need column header to be displayed on the top, so modified it like this

demo@XEPDB1> create or replace package body pkg_export_to_csv
  2  as
  3    g_str long;
  4
  5    function describe( p_tab in out dbms_tf.table_t )
  6    return dbms_tf.describe_t
  7    as
  8               l_new_cols dbms_tf.COLUMNS_NEW_T;
  9    begin
 10               for i in 1..p_tab.column.count
 11               loop
 12                             p_tab.column(i).pass_through := false;
 13                             p_tab.column(i).for_read := true;
 14                             g_str := g_str ||','|| p_tab.column(i).description.name;
 15               end loop;
 16                             g_str := trim(',' from g_str);
 17               l_new_cols( l_new_cols.count+1 ) := dbms_tf.COLUMN_METADATA_T( name => 'csv_cols' );
 18               return dbms_tf.describe_t( new_columns => l_new_cols );
 19    end;
 20
 21    procedure fetch_rows as
 22               l_env dbms_tf.env_t ;
 23               l_rowset dbms_tf.row_set_t;
 24               l_rowcnt number;
 25               l_colcnt number;
 26               l_piece long;
 27               l_rows number := 0 ;
 28               l_value dbms_tf.tab_varchar2_t ;
 29    begin
 30               l_env := dbms_tf.get_env;
 31               dbms_tf.get_row_set( l_rowset,l_rowcnt,l_colcnt );
 32
 33               for r in 1..l_rowcnt
 34               loop
 35                               dbms_tf.xstore_get('X'||r,l_rows);
 36                               l_piece := null;
 37                               for c in 1..l_colcnt
 38                               loop
 39                                               l_piece := l_piece ||','||
 40                                                               dbms_tf.col_to_char( l_rowset(c),r );
 41                               end loop;
 42                               l_value(r) := trim(',' from l_piece);
 43                               dbms_tf.xstore_set('X'||r,l_rows+l_rowcnt);
 44               end loop;
 45                     l_value(0) := g_str;
 46               if l_colcnt > 0 then
 47                               dbms_tf.put_col(1,l_value);
 48               end if;
 49    end;
 50  end;
 51  /

Package body created.

demo@XEPDB1> select * from export_to_csv(dept);

CSV_COLS
------------------------------
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

demo@XEPDB1>

but it doesn't help, so went with this workaround.

demo@XEPDB1> with rws as (
  2  select 'DEPTNO' as deptno,'DNAME' as dname,
  3      'LOC' as loc
  4  from dual
  5  union all
  6  select to_char(deptno),dname,loc from dept )
  7  select * from export_to_csv(rws)
  8  /

CSV_COLS
------------------------------
"DEPTNO","DNAME","LOC"
"10","ACCOUNTING","NEW YORK"
"20","RESEARCH","DALLAS"
"30","SALES","CHICAGO"
"40","OPERATIONS","BOSTON"

do we have any better option to handle the column header dynamically in the PTF implementation?

SQL> create or replace package pkg_export_to_csv
  2    as
  3       function describe( p_tab in out dbms_tf.table_t )
  4       return dbms_tf.describe_t;
  5
  6       procedure fetch_rows ;
  7    end;
  8    /

Package created.

SQL>
SQL> create or replace package body pkg_export_to_csv
  2     as
  3        function describe( p_tab in out dbms_tf.table_t )
  4        return dbms_tf.describe_t
  5        as
  6                l_new_cols dbms_tf.COLUMNS_NEW_T;
  7        begin
  8                for i in 1..p_tab.column.count
  9                loop
 10                                p_tab.column(i).pass_through := false;
 11                                p_tab.column(i).for_read := true;
 12                end loop;
 13
 14                l_new_cols( l_new_cols.count+1 ) := dbms_tf.COLUMN_METADATA_T( name => 'csv_cols' );
 15                return dbms_tf.describe_t( new_columns => l_new_cols, row_replication => true );
 16        end;
 17
 18        procedure fetch_rows as
 19                l_env dbms_tf.env_t ;
 20                l_rowset dbms_tf.row_set_t;
 21                l_rowcnt number;
 22                l_colcnt number;
 23                l_piece long;
 24                l_rows number := 0 ;
 25                l_value dbms_tf.tab_varchar2_t ;
 26
 27                repfac dbms_tf.tab_naturaln_t;
 28                new_r int := 0;
 29        begin
 30                l_env := dbms_tf.get_env;
 31                dbms_tf.get_row_set( l_rowset,l_rowcnt,l_colcnt );
 32
 33                for r in 1..l_rowcnt
 34                loop
 35                   repfac(r) := 1;
 36                   if r = 1 then
 37                     new_r := 1;
 38                     repfac(r) := repfac(r) + 1;
 39                     dbms_tf.xstore_get('X'||r,l_rows);
 40                     l_piece := null;
 41                     for c in 1..l_colcnt
 42                     loop
 43                            l_piece := l_piece ||','||l_env.get_columns(c).name;
 44                     end loop;
 45                     l_value(new_r) := trim(',' from l_piece);
 46                     dbms_tf.xstore_set('X'||new_r,l_rows+l_rowcnt);
 47
 48                     new_r := 2;
 49                     l_piece := null;
 50                     for c in 1..l_colcnt
 51                     loop
 52                            l_piece := l_piece ||','||dbms_tf.col_to_char( l_rowset(c),r );
 53                     end loop;
 54                     l_value(new_r) := trim(',' from l_piece);
 55                     dbms_tf.xstore_set('X'||new_r,l_rows+l_rowcnt);
 56
 57                   else
 58                     new_r := r + 1;
 59                     dbms_tf.xstore_get('X'||r,l_rows);
 60                     l_piece := null;
 61                     for c in 1..l_colcnt
 62                     loop
 63                            l_piece := l_piece ||','||dbms_tf.col_to_char( l_rowset(c),r );
 64                     end loop;
 65                     l_value(new_r) := trim(',' from l_piece);
 66                     dbms_tf.xstore_set('X'||new_r,l_rows+l_rowcnt);
 67                   end if;
 68
 69                end loop;
 70
 71                if l_colcnt > 0 then
 72                        dbms_tf.row_replication(replication_factor => repfac);
 73                        dbms_tf.put_col(1,l_value);
 74                end if;
 75        end;
 76     end;
 77     /

Package body created.

SQL>
SQL> sho err
No errors.
SQL>
SQL>
SQL> create or replace function export_to_csv(
  2    p_tab in table )
  3  return
  4  table pipelined
  5  table polymorphic
  6  using pkg_export_to_csv;
  7  /

Function created.

SQL>
SQL> select * from export_to_csv(dept);

CSV_COLS
----------------------------------------------------------------------------------------------------------------------------------
"DEPTNO","DNAME","LOC"
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"

5 rows selected.

SQL>

Team,

thanks for the above response, completely forgot about the "replication factor" implementation - thanks for highlighting that.

we got a requriement in application to unload the data in CSV format.
so we thought of developing a Polymorphic table function(PTF)utility for this.
while benchmarking (PTF vs Manual concatenation) in lower environment we found that PTF consume more CPU.

so how can we make PTF to be CPU efficient? Kindly advice.

Below is our benchmarking details.

create or replace package pkg_export_to_csv
as
   function describe( p_tab in out dbms_tf.table_t )
   return dbms_tf.describe_t;

   procedure fetch_rows ;
end;
/

create or replace package body pkg_export_to_csv
as
  function describe( p_tab in out dbms_tf.table_t )
  return dbms_tf.describe_t
  as
    l_new_cols dbms_tf.COLUMNS_NEW_T;
  begin
    for i in 1..p_tab.column.count
    loop
        p_tab.column(i).pass_through := false;
        p_tab.column(i).for_read := true;
    end loop;

    l_new_cols( l_new_cols.count+1 ) := dbms_tf.COLUMN_METADATA_T( name => 'csv_cols' );
    return dbms_tf.describe_t( new_columns => l_new_cols );
  end;

  procedure fetch_rows as
    l_env dbms_tf.env_t ;
    l_rowset dbms_tf.row_set_t;
    l_rowcnt number;
    l_colcnt number;
    l_piece long;
    l_rows number := 0 ;
    l_value dbms_tf.tab_varchar2_t ;
  begin
    l_env := dbms_tf.get_env;
    dbms_tf.get_row_set( l_rowset,l_rowcnt,l_colcnt );

    for r in 1..l_rowcnt
    loop
      dbms_tf.xstore_get('X'||r,l_rows);
      l_piece := null;
      for c in 1..l_colcnt
      loop
        l_piece := l_piece ||','||
          dbms_tf.col_to_char( l_rowset(c),r );
      end loop;
      l_value(r) := trim(',' from l_piece);
      dbms_tf.xstore_set('X'||r,l_rows+l_rowcnt);
    end loop;

    if l_colcnt > 0 then
      dbms_tf.put_col(1,l_value);
    end if;
  end;
end;
/

create or replace function export_to_csv(
   p_tab in table )
return
table pipelined
table polymorphic
using pkg_export_to_csv;
/

create table t1 
nologging as 
select owner,object_type,object_id,
 object_name, rpad('*',1000,'*') as data
from all_objects a,
 all_users 
where rownum <=100000; 

exec dbms_monitor.SESSION_TRACE_ENABLE( waits=>true, binds=>true );

begin 
 for x in ( select owner||','||object_type||','||object_id||','||
   object_name||','||data txt 
   from t1 )
 loop
  null ;
 end loop;
end;
/ 

begin 
 for x in (select * from export_to_csv(t1) )
 loop
  null ;
 end loop;
end;
/ 

Here is what the Tkprof shows us

********************************************************************************

SQL ID: 0xum9g49ndmfg Plan Hash: 3617692013

SELECT * 
FROM
 EXPORT_TO_CSV(T1) 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.02          0        786          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1001    119.22     158.21      16139      24458          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1003    119.26     158.24      16139      25244          0      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    100000     100000     100000  POLYMORPHIC TABLE FUNCTION  EXPORT_TO_CSV (cr=27202 pr=16139 pw=0 time=2997615101 us starts=1)
    100000     100000     100000   TABLE ACCESS FULL T1 (cr=16226 pr=16139 pw=0 time=2983240 us starts=1 cost=4427 size=103300000 card=100000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                          127        0.00          0.00
  direct path read                              414        0.19          2.80



********************************************************************************

SQL ID: dz5zwy6xfta5c Plan Hash: 3617692013

SELECT OWNER||','||OBJECT_TYPE||','||OBJECT_ID||','|| OBJECT_NAME||','||DATA 
  TXT 
FROM
 T1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          6           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1001      0.48       3.19      16139      16978          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1003      0.48       3.21      16139      16978          6      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    100000     100000     100000  TABLE ACCESS FULL T1 (cr=16978 pr=16139 pw=0 time=1341619 us starts=1 cost=4427 size=103300000 card=100000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                1        0.04          0.04
  PGA memory operation                            3        0.00          0.00
  direct path read                              492        0.53          2.67
********************************************************************************