RE: Pipelined table Functions

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Fri, 26 Mar 2004 12:26:53 -0500
Message-ID: <D17DB304A9F42B4787B68861F9DAE61C033E7E@wgdc02.wgenhq.net>

Sushman,
Since you are piping the data out, you are not returning the result set
in a "suspended" fashion. You don't fill an array and return it as a result set. Actaully, you don't even declare an array, you just need it as a return type So, You get a value, pipe it out, get it, pipe it ...

looking at this example:

create type x as object ( a number, b date ); /

create type y as table of x;
/

create function fpiped ( n number )
return y pipelined
as
begin for i in (
  select x(rownum, sysdate+rownum) l_rec     from all_objects
   where rownum <= n
)
loop
  pipe row ( i.l_rec );
end loop;
return;
end fpiped;
/

SQL> select * from table(fpiped(10));

A B
---------- ---------

         1 27-MAR-04
         2 28-MAR-04
         3 29-MAR-04
         4 30-MAR-04
         5 31-MAR-04
         6 01-APR-04
         7 02-APR-04
         8 03-APR-04
         9 04-APR-04
        10 05-APR-04

10 rows selected.

Based on that, you don't really need to free the memory used by the array
since you don't really use one ( although I think under the covers you do ).
Also, you can't really use a forall with this ( is that what you meant by bulk? ) since it's not really an array. I believe the memory comes out of
the pga but should be much less due to the fact that you are piping out the
rows and not filling up a huge array.

If you were filling up an array, I would consider immediately calling it's
.delete() method right after you are done with it.

-----Original Message-----

From: sushma manjunath [mailto:sushmam_at_hotmail.com] Sent: Friday, March 26, 2004 12:01 PM
To: oracle-l_at_freelists.org
Subject: Pipelined table Functions

All,

I am investingating usage of Pipelined Table Functions for Transformation and Load into the datawarehouse. The source data is about a
million records. I am using a ref cursor to select the data from the source
and perform data transformations and pipe the row. Finally, a bulk insert
from the table function to the database. Question is where is the table data
stored when I pipe it. I need to monitor space usage to determine if this a
feasibility. Also, how does the space assigned to the Table function shrink
later.

Thanks for all your help in advance.

Sushma

All the action. All the drama. Get NCAA hoops coverage at MSN Sports by ESPN. http://msn.espn.go.com/index.html?partnersite=espn

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com


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

To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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

Received on Fri Mar 26 2004 - 11:23:23 CST