Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Oracle 10g Returning Large Amounts of Data from a Function

A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions. Prior to Oracle Database 10g, large transformations required either significant memory overhead, or storing the data in intermediate tables between each stage of the transformation. The loading process caused immense performance degradations in both cases.

Using PL/SQL table functions can significantly lower the over-head of doing such transformations. PL/SQL table functions accept and return multiple rows, delivering them as they are ready rather than all at once, and can be made to execute as parallel operations.

To Use PL/SQL Table Functions in this manner:

• The producer function must use the PIPELINED keyword in its declaration.
   

• The producer function must use an OUT parameter that is a record, corresponding to a row in the result set.
   

• Once each output record is completed, it is sent to the consumer function through the use of the PIPE ROW keyword.
   

• The producer function must end with a RETURN statement that does not specify any return value.
   

• The consumer function or SQL statement then must use the TABLE keyword to treat the resulting rows from the PIPELINE function like a regular table.

For example:

CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN
TickerTypeSet PIPELINED
IS
out_rec TickerType := TickerType(NULL,NULL,NULL);
in_rec p%ROWTYPE;
BEGIN
LOOP
-- Function accepts multiple rows through a REF CURSOR
-- argument.
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;
-- Return value is a record type that matches the table
-- definition.
out_rec.ticker := in_rec.Ticker;
out_rec.PriceType := 'O';

Get the complete Oracle10g story:

The above text is an excerpt from "Oracle Database 10g New Features: Oracle10g Reference for Advanced Tuning and Administration", by Rampant TechPress.  Written by top Oracle experts, this book has a complete online code deport with ready to use scripts. 

To get the code instantly, click here:

http://rampant-books.com/book_2003_2_oracle10g.htm

Need an Oracle Mentor?

BEI is now offering personal mentors for Oracle DBAs where you can have an Oracle expert right at your fingertips, anytime day or night. We work with hundreds of Oracle databases every year, so we know exactly how to quickly assist you with any Oracle question.

Why risk an unplanned outage? You can now get telephone access to Don Burleson or any of his Oracle Certified DBAs with more than 20 years of full-time IT experience. Click here for details:

http://www.dba-oracle.com/service_oracle_backup.htm