Database: Pipelined Functions
To quote fom "Ask Tom Oracle":
pipelined functions are simply "code you can pretend is a database table"
pipelined functions give you the (amazing to me) ability to
select * from PLSQL_FUNCTION;anytime you think you can use it -- to select * from a function, instead of a table, it
might be "useful".
As far as advantages: a big advantage of using a Pipeline function is that your function can return rows one-by-one as opposed to building the entire result set in memory as a whole before returning it.
The above gives the obvious optimization - memory savings from something that would otherwise return big result set.
A fairly interesting example of using pipelined functions is here
What seems to be a good use of them is ETL (extract/transform/load) - for example see here
I can't call the function using jdbc, but if it is pipelined, then everything works
A RECORD is a PL/SQL ONLY data-type that cannot be used in SQL statements.
A PIPELINED function is designed to be used in SQL statements and despite you declaring that it returns a table of RECORD data types, it does not actually do that and, instead, creates equivalent SQL data-types (i.e. OBJECT data-types) that can be used in SQL statements.
If you want both to work then declare o_client and t_client in the SQL scope using:
CREATE TYPE o_client IS OBJECT (subs_id NUMBER);
CREATE TYPE t_o_client IS TABLE OF o_client;
Then you can use SQL data-types and not PL/SQL data-types:
CREATE PACKAGE pkg IS
type r_client is record(subs_id NUMBER);
type t_client is table of r_client;
function pipelined_func return t_client pipelined;
function no_pipelined_func return t_client;
function no_pipelined_func_obj return t_o_client;
END;
/
and the body:
CREATE PACKAGE BODY pkg IS
FUNCTION pipelined_func
RETURN t_client PIPELINED
AS
v_pipe r_client;
BEGIN
FOR ids IN 1..10 LOOP
v_pipe.subs_id := ids;
PIPE ROW(v_pipe);
END LOOP;
END;
FUNCTION no_pipelined_func
RETURN t_client
AS
l_tab t_client;
v_pipe r_client;
BEGIN
l_tab := t_client();
FOR ids IN 1..10 LOOP
v_pipe.subs_id := ids;
l_tab.extend;
l_tab(l_tab.last) := v_pipe;
END LOOP;
RETURN l_tab;
END;
FUNCTION no_pipelined_func_obj
RETURN t_o_client
AS
l_tab t_o_client := t_o_client();
BEGIN
FOR ids IN 1..10 LOOP
l_tab.extend;
l_tab(l_tab.last) := o_client(ids);
END LOOP;
RETURN l_tab;
END;
END;
/
Then:
SELECT * FROM pkg.no_pipelined_func();
Fails with:
ORA-00902: invalid datatype
But:
SELECT * FROM pkg.pipelined_func();
and
SELECT * FROM pkg.no_pipelined_func_obj();
Both output:
SUBS_ID 1 2 3 4 5 6 7 8 9 10
How to choose between pipelined table function , view , and explicit cusor
With both cursor types, the database executes the statement within it when it opens. Provided the cursor remains open, you can fetch the results from it later without re-running it. So both are equally efficient in that respect.
An explicit cursor is one where you control its full lifecycle: open, fetch, and close. With an implicit cursor, PL/SQL handles this for you.
You use an explicit cursor when you want full control over the fetch process. The main use case for this bulk collection with a limit.
An explicit cursor can also be handy if you want to use the same query in many places in your application. Declare it at the package level and you can reference it anywhere else you like:
create or replace package pkg as
cursor common_cursor is
select ...
end;This gives a single definition for the query, which can make your code more maintainable. The problem with this is you're on the hook for opening, fetching, and closing it wherever you use it. In most cases, this results in much more code for minimal benefit.
Which brings us to views. Instead of declaring the common cursor, you could place the common query in a view:
create or replace view common_query as
select ...;You can then use this in any other SQL statement just like a regular table. So you can join to it, etc. You can't do this with an explicit cursor directly. You have to wrap it in a (pipelined) table function:
create or replace function pipetf
return ...
pipelined
as
retvals ...;
begin
open pkg.common_cursor;
loop
fetch pkg.common_cursor
bulk collect into retvals limit 100;
exit when retvals.count = 0;
for i in 1 .. retvals.count loop
pipe row ( retvals ( i ) ) ;
end loop;
end loop;
close pkg.common_cursor ;
return;
end pipetf;
/This allows you to use the cursor within another SQL statement like a view:
select * from pipetf;At this point, a pipelined table function seems a lot more faff than a view. So why bother?
Well it allows you to do things views can't (easily):
- Generate new rows or manipulate the result set procedurally
- Create parameterized queries
In general you can't pass a variable to a query like this a view (there are ways, but they come with gotchas):
select c2 from ...
where c1 = :var
group by c2;Whereas you can in an explicit cursor:
cursor common_cursor ( var int ) is
select c2 from ...
where c1 = var
group by c2;So you could use this in a PTF to create a reusable, parameterized query:
create or replace function pipetf ( var int )
return ...
pipelined
as
retvals ...;
begin
open pkg.common_cursor ( var );
loop
fetch pkg.common_cursor
bulk collect into retvals limit 100;
exit when retvals.count = 0;
for i in 1 .. retvals.count loop
pipe row ( retvals ( i ) ) ;
end loop;
end loop;
close pkg.common_cursor ;
return;
end pipetf;
/So if you need to use PL/SQL to create new rows, manipulate a queries results, or want reusable parameterized queries, pipelined table functions were the way to go.
Why were?
Oracle Database 18c added polymorphic table functions, which covers many of the row generation/result manipulation examples. And from 19.6 you can create SQL macros, which you can use to emulate parameterized views. These features cover most (all?) the use cases for pipelined table functions (and more).
If you just need a reusable query with no extra processing, I'd stick with a view.
Oracle Query over Pipeline Function - Strange Error when Data contains ASCII Extended Characters
When you do:
IF LENGTH(v_buffer) + c_eollen + LENGTH(v_rows(i)) <= c_maxlineyou are counting the number of characters in the buffer and collection variables. When you only have single-byte characters that's OK, but with any multibyte characters you could get a situation where the number of characters totals less than 32767, but the number of bytes exceeds that. The check passes; but then you do:
v_buffer := v_buffer || c_eol || v_rows(i)which exceeds the size of the buffer, and throws the error. If your buffer was declared smaller than the maximum and using character semantics you might still get away with that; but with the maximum size (and any semantics) it will fail.
If you count bytes instead of characters it won't exceed the byte limit:
IF LENGTHB(v_buffer) + c_eollen + LENGTHB(v_rows(i)) <= c_maxlinePipelining Between PL/SQL Table Functions
The function
pipe_func_emp2expectedRESULTING_COLS_RTas it's argument, but gotREF CURSOR. These are incompatible types.Try following reproducible example of a chaining of the pipelined functions:
create or replace type somerow as object (id int, val varchar2 (8))
/
create or replace type sometab is table of somerow
/
create or replace package pack as
function func1 return sometab pipelined;
function func2 (cur sys_refcursor) return sometab pipelined;
end;
/
create or replace package body pack as
function func1 return sometab pipelined is
tab sometab := sometab (somerow (1,'AAA'), somerow (2,'BBB'));
begin
for i in 1..tab.count loop
pipe row (tab(i));
end loop;
return;
end;
function func2 (cur sys_refcursor) return sometab pipelined is
sr somerow;
begin
loop
fetch cur into sr;
exit when cur%notfound;
pipe row (sr);
end loop;
close cur;
return;
end;
end;
/The query and it's outcome:
select *
from table (pack.func2 (
cursor (select value (p) from table (pack.func1()) p )))
/
ID VAL
---------- --------
1 AAA
2 BBBNested PIPELINED function in pl/sql
Pipelined functions provide rows one by one (on demand), so you cannot put all the rows at one time from pipelined function.
Seems to me you need to change
main_xyzthis way:function main_xyz return data_type_1 pipelined is
begin
--code
FOR rec IN (select * from table(XYZ.sub_func)) LOOP
pipe row(rec);
END LOOP;
end;Consider that
sub_funcmust be in specification of XYZ package since everything you use in SQL queries including PIPELINED functions are to be public (i.e. visible to a user who runs query).UPDATE: I forget to alert: do not abuse pipelined functions (if you have another choice) - queries using them might have lame performance because DB engine cannot build a good execution plan for "unpredictable piped rows".
Pipelined function doesn't return a table with errors
What am I doing wrong? How to fix it? I could've done this in a for
cycle, but isn't it too inefficient for required purpose?If you inspect the error properly you could see the error. The error says:
Local Collection Types are not allowed in SQL statement
Which means in your execution block:
insert into T_TEST(col01, col02) select n_num, column_value from
table(tn_test);Above statement is
NOT ALLOWED.Until Oracle 11g, you cannot use a
Typedeclared under the scope ifPLSQLblock directly underSQLstatement used inside the block. You need to change the scope of declaration ofTypeoutside thePLSQLscope. Which means, you need toREMOVE
type t_num is table of number;from the package specification and create aTYPEoutside the inSQLscope.So you can do this:
Create or replace type t_num is table of number;See below demo:
create table t_test (col01 number, col02 number);
-- Moving the type decalration under the scope of SQL.
Create or replace type t_num is table of number;
create or replace package p_test is
-- type t_num is table of number; --<-- Commenting the declaration since this is not allowed until 11g.
function rtn(arg_tn t_num)
return t_num PIPELINED;
end p_test;
/
create or replace package body p_test is
function rtn(arg_tn t_num)
return t_num PIPELINED
is
tn_row number;
begin
for i in arg_tn.first .. arg_tn.last
loop
tn_row := arg_tn(i);
pipe row(tn_row);
end loop;
return;
end;
end p_test;Execution:
declare
tn_test t_num := t_num(10, 20, 30);
n_num number := 69;
begin
insert into T_TEST
(col01,
col02)
select n_num,
column_value
from table(tn_test);
commit;
end;Test:
SQL> Select * from T_TEST;
COL01 COL02
---------- ----------
69 10
69 20
69 30
Related Topics
SQL Server - Does Trigger Affects @@Rowcount
Deleting a Row with a Self-Referencing Foreign Key
Ssrs Report Builder - Only Show Header on First Page (With Page Numbers)
What's a Zip Join? Have You Ever Heard of That, or a Pairwise Join
Native Insert Query in Hibernate + Spring Data
How to Use a SQL Select Statement with Access Vba
How Does One Escape an Apostrophe in Db2 SQL
Error (Ora-00923: from Keyword Not Found Where Expected)
T:Sql: Select Values from Rows as Columns
Convert Rows to Columns Using 'Pivot' in Mssql When Columns Are String Data Type
SQL Question: Does the Order of the Where Clause Make a Difference
Convert Exponential to Number in SQL
How to Insert into Two Tables All at Once in a Stored Procedure
Snowflake: "SQL Compilation Error:... Is Not a Valid Group by Expression"
Reference Value of Serial Column in Another Column During Same Insert