USE :
--------------------------
Pipelined functions are useful if there is a need for a data source other than a table in a select statement.
What is pipelined function :
--------------------------
Pipelining negates the need to build huge collections by piping rows out of the function as they are created,
saving memory and allowing subsequent processing to start before all the rows are generated.
Pipelined table functions include the PIPELINED clause and use the PIPE ROW call to push rows out of the function
as soon as they are created, rather than building up a table collection. Notice the empty RETURN call, since there
is no collection to return from the function.
if you mean "pipelined functions" it would be the fact that you are streaming data from a cursor to a procedure process (the pipelined function) and then another process retrieves this. and you can skip all of the heinous IO that a global temporary table would likely incur by just streaming the data.
and the stream is nice because the pipelined function is constantly returning data to the client as it produces it - nice "pipeline" gets going instead of a series of monolithic steps that require large amounts of resources. This constant return is vary from tool to tool.
Demo :
------------------------------
Normal method of doing passing data to application.
------------------------------
Table functions are used to return PL/SQL collections that mimic tables.They can be queried like a regular table by using the TABLE function in the FROM clause.Regular table functions require collections to be fully populated before they are returned.Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row.
These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations.
Regular table functions require named row and table types to be created as database objects.
create table album_scalar_table
(a_id VARCHAR2(10),
flags VARCHAR2(1)
) ;
CREATE OR REPLACE FUNCTION flags_etl_table
RETURN varchar2 AS
v_a_id VARCHAR2(10);
v_a_flag VARCHAR2(10);
v_flg VARCHAR2(1);
BEGIN
LOOP
FOR I IN 1..
LOOP
insert into album_scalar_table values (I, I);
END LOOP;
END LOOP;
RETURN 'A';
END;
/
declare
ret_val varchar(200);
begin
ret_val:=flags_etl_table;
end;
/
select * from album_scalar_table;
-- it should wait the completion of this function and transmite the data;
------------------------------
Normal method of doing passing data to application. with table functions : Table Functions
------------------------------
create or replace type album_scalar_type as object
(a_id VARCHAR2(10),
flags VARCHAR2(1)
);
create or replace type album_table_type as table of album_scalar_type;
-- Build the table function itself.
CREATE OR REPLACE FUNCTION get_tab_tf RETURN album_table_type AS
l_tab album_table_type := album_table_type();
BEGIN
FOR i IN 1 .. 100000000000000000000000000
l_tab.extend;
l_tab(l_tab.last) := album_scalar_type(i, 1);
END LOOP;
RETURN l_tab;
END;
/
select * from table(get_tab_tf);
Note : both the case the the end select statement would wait the calling function to complete and return the rows .
------------------------------
pipelined method of doing passing data to application.
------------------------------
create or replace type album_scalar_type as object
(a_id VARCHAR2(10),
flags VARCHAR2(1)
)
create or replace type album_table_type as table of album_scalar_type;
CREATE OR REPLACE FUNCTION flags_etl
RETURN album_table_type PIPELINED AS
v_a_id VARCHAR2(10);
v_a_flag VARCHAR2(10);
v_flg VARCHAR2(1);
BEGIN
LOOP
FOR I IN 1..100000000000000000000000000
LOOP
pipe row( album_scalar_type(I, 1) );
END LOOP;
END LOOP;
RETURN;
END;
/
select * from table(flags_etl());
-- this will return the rows immediately when the return interval reaches, means that whenever the rows are getting inserted into the array , oracle automatically
------------------------------
Demo -- will that entire loop getting executed ????
------------------------------
CREATE OR REPLACE FUNCTION flags_etl
RETURN album_table_type PIPELINED AS
v_a_id VARCHAR2(10);
v_a_flag VARCHAR2(10);
v_flg VARCHAR2(1);
BEGIN
LOOP
FOR I IN 1..1000
LOOP
DBMS_LOCK.sleep(seconds =>.1);
pipe row( album_scalar_type(I, 1) );
insert into album_scalar_table values (I, I);
END LOOP;
END LOOP;
RETURN;
END;
/
select * from table(flags_etl())
where rownum <=20
-- result of error.. cannot perform a DML operation inside a query.
--advantage of no need of wait until the function completes.
CREATE OR REPLACE FUNCTION flags_etl
RETURN album_table_type PIPELINED AS
v_a_id VARCHAR2(10);
v_a_flag VARCHAR2(10);
v_flg VARCHAR2(1);
BEGIN
LOOP
FOR I IN 1..1000
LOOP
pipe row( album_scalar_type(I, 1) );
dbms_output.put_line (I);
END LOOP;
END LOOP;
RETURN;
END;
/
select * from table(flags_etl())
where rownum <=100;
select * from table(flags_etl())A
where rownum <=100
order by a.a_id desc
-- so the loop will get terminated only 100 times.
-- so this wont wait until function completes
------------------------------
Demo -- NO_DATA_NEEDED
------------------------------
A pipelined table function may create more data than is needed by the process querying it. When this happens,
the pipelined table function execution stops, raising the NO_DATA_NEEDED exception.
This doesn't need to be explicitly handled provided you do not include an OTHERS exception handler.
CREATE OR REPLACE FUNCTION flags_etl
RETURN album_table_type PIPELINED AS
v_a_id VARCHAR2(10);
v_a_flag VARCHAR2(10);
v_flg VARCHAR2(1);
BEGIN
LOOP
FOR I IN 1..1000
LOOP
pipe row( album_scalar_type(I, 1) );
dbms_output.put_line (I);
END LOOP;
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('OTHERS Handler');
RAISE;
END;
/
select * from table(flags_etl())
where rownum <=100;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
OTHERS Handler
