8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Pipelined Table 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

Rohit Vishwakarma said...

When I run this query, I get invalid identifier error. I followed all the instructions in this tutorial. created t_tf_row, t_tf_tab and also created the function without any warnings.

SELECT * FROM TABLE(get_tab_tf(10)) ORDER BY id DESC;
ORA-00904: "GET_TAB_TF": invalid identifier
Script line 8, statement line 1, column 21 

thanks in advance.

Rohit Vishwakarma said...

please disregard this. this error was caused by aqua data studio doing something crazy with table function. When i created the same function with SQL plus, I did not get any errors.

Tim... said...

Hi.

No worries. At least that saves me having to think. :)

Cheers

Tim...

SajjaD said...

What about if I defined record and table types in a package specification and want "get_tab_ptf" function without pipelining?

Tim... said...

Hi.

Then it is not a pipelined table function and shadow types would not work. When you try and query the function, it will give you an invalid type error. It will compile though.

This is why the section starts with the words, "Unlike regular table functions, ...". :)

Cheers

Tim...

SajjaD said...

So is there anyway to do in that way (I mean without pipeling) ?
Because if I define all of types that i need in my procedures and functions with "CREATE OR REPLACE TYPE..." , it will be confusing so i want to define each of them in it's package's header.

Tim... said...

Hi.

Not possible. Shadow types are specifically for PTFs. If you want everything in your code, you must use PTFs.

Cheers

Tim...

SajjaD said...

OK...Thanks Tim

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!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.