Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Pipelined Table Functions - Improve performance of ETL processes by pipelining all transformation functions.



Sarah said...

This was helpful... I discovered a limitation when using pipelined tables stored in a variable. When I attempted to use one in a SELECT used for an INSERT, compiling the package disconnected me from Oracle! The pipelining function must be called directly in the select statement. Using the variable in UPDATEs and MERGEs worked fine.

In package spec:
TYPE work_row_typ IS
RECORD (ID work.id%TYPE, and so on);
TYPE work_tbl_typ IS TABLE OF work_row_typ;

In package body:

function GET_WORKLOAD_DATA
return MYPKG.work_tbl_typ pipelined
IS
blah
END GET_WORKLOAD_DATA;

function main_fcn(whatever) return integer IS
tblWork MYPKG.work_tbl_typ;
begin
This caused compile to disconnect from Oracle:
SELECT * INTO tblWork
FROM TABLE(GET_WORKLOAD_DATA());

This compiled without error:
SELECT * BULK COLLECT INTO tblWork
FROM TABLE(GET_WORKLOAD_DATA());

This caused compile to disconnect from Oracle:
INSERT INTO MyTbl
SELECT col list FROM TABLE(tblWork);

This compiled without error:
INSERT INTO MyTbl
SELECT col list
FROM TABLE(GET_WORKLOAD_DATA());

I put the results of the function into a variable because it is needed in multiple SQL statements. Didn't want to call the function multiple times. Data is only deterministic in the short run, so didn't want to use that.

Rachit said...

So far the most correct explanation of 'why Piped?'....

sharan said...

very helpful thnx

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

Add your comments here.
Name
Comment
(max 400 chars - plain text)