RE: Pipelined table Functions
- From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 26 Mar 2004 12:26:53 -0500
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.
- ant
-----Original Message-----
From: sushma manjunath [mailto:sushmam@xxxxxxxxxxx]
Sent: Friday, March 26, 2004 12:01 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
