I have had trouble with a certain view I have to create at the customer site I am currently working at. The view involves 3 SQL
queries combined by using a UNION ALL, since the separate queries are mutually exclusive. Using the UNION ALL makes it a bit faster since
Oracle doesn’t have to do all the work of sorting and checking for duplicate rows.
Retrieving data from this view resulted in memory errors since there is too much data for the server to come up with and to return to me.
After checking all kinds of initialization parameters I decided to try and build a couple of pipelined table functions that would retrieve
the same data for me, but I assumed it wouldn’t put such a big pressure on my memory usage.
A pipelined table function will come up with a single row for the result set and pipe it out of the function when it’s available. In my
opinion there would only be need for enough memory for this single row instead of enough memory for all the rows of the entire result set.
Since the function gets called from SQL the SQL engine needs to be aware of the result it produces. So I need to create types that define
a row of the result. And since the function will return more than a single row, I also need to define a NESTED TABLE, a collection of
these rows. Using two types I can mimic the appearance of a table.
Now all I have to do is create functions that will give me the result I am interested in.
My ‘normal’ view looks like this:
create or replace view v_emps as
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
from emp e
where e.deptno = 10
union all
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
from emp e
where e.deptno = 20
union all
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
from emp e
where e.deptno = 30
union all
select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm
from emp e
where e.deptno = 40
/
In the following example I create a new view based on the table functions for the EMP table. The SQL approach is better, easier to read and faster, but it shows what should be done if you want to move over to table functions.
The way such a function look is kind of strange at first sight.
Function f1 return mynestedtabletype pipelined
Is
<declaration>
l_record myrecordtype;
begin
loop
<do stuff to define l_record>
pipe row(l_record); — send the row out
end loop;
return; — return nothing but control
end f1;
First I create a type for the records I return from my function:
CREATE TYPE emp_t AS OBJECT
(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2)
)
/
Then I create a nested table of the record type I just created. The functions will return (or say they will return) a variable of this type:
CREATE TYPE emps_tt AS TABLE OF emp_t
/
Next is the package which will contain the table functions. I create 4 functions to retrieve all the datasets I need.
CREATE OR REPLACE PACKAGE emp_tf IS
— Author : Patrick Barel
— Purpose : Table Functions for EMP
— Public function and procedure declarations
FUNCTION get_accounting RETURN emps_tt PIPELINED;
FUNCTION get_research RETURN emps_tt PIPELINED;
FUNCTION get_sales RETURN emps_tt PIPELINED;
FUNCTION get_operations RETURN emps_tt PIPELINED;
END emp_tf;
/
CREATE OR REPLACE PACKAGE BODY emp_tf IS
— Function and procedure implementations
FUNCTION get_accounting RETURN emps_tt
PIPELINED IS
CURSOR c_emps IS
SELECT e.empno, e.ename, e.job, e.mgr
,e.hiredate, e.sal, e.comm
FROM emp e
WHERE e.deptno = 10;
r_emps c_emps%ROWTYPE;
l_returnvalue emp_t;
BEGIN
OPEN c_emps;
LOOP
FETCH c_emps INTO r_emps;
l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
,r_emps.mgr, r_emps.hiredate
,r_emps.sal, r_emps.comm);
EXIT WHEN c_emps%NOTFOUND;
PIPE ROW(l_returnvalue);
END LOOP;
CLOSE c_emps;
RETURN;
END get_accounting;
—
FUNCTION get_research RETURN emps_tt
PIPELINED IS
CURSOR c_emps IS
SELECT e.empno, e.ename, e.job, e.mgr
,e.hiredate, e.sal, e.comm
FROM emp e
WHERE e.deptno = 20;
r_emps c_emps%ROWTYPE;
l_returnvalue emp_t;
BEGIN
OPEN c_emps;
LOOP
FETCH c_emps
INTO r_emps;
l_returnvalue := emp_t(r_
em
ps.empno, r_emps.ename, r_emps.job
,r_emps.mgr, r_emps.hiredate
,r_emps.sal, r_emps.comm);
EXIT WHEN c_emps%NOTFOUND;
PIPE ROW(l_returnvalue);
END LOOP;
CLOSE c_emps;
RETURN;
END get_research;
—
FUNCTION get_sales RETURN emps_tt
PIPELINED IS
CURSOR c_emps IS
SELECT e.empno, e.ename, e.job, e.mgr
,e.hiredate, e.sal, e.comm
FROM emp e
WHERE e.deptno = 30;
r_emps c_emps%ROWTYPE;
l_returnvalue emp_t;
BEGIN
OPEN c_emps;
LOOP
FETCH c_emps
INTO r_emps;
l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
,r_emps.mgr, r_emps.hiredate
,r_emps.sal, r_emps.comm);
EXIT WHEN c_emps%NOTFOUND;
PIPE ROW(l_returnvalue);
END LOOP;
CLOSE c_emps;
RETURN;
END get_sales;
—
FUNCTION get_operations RETURN emps_tt
PIPELINED IS
CURSOR c_emps IS
SELECT e.empno, e.ename, e.job, e.mgr
,e.hiredate, e.sal, e.comm
FROM emp e
WHERE e.deptno = 40;
r_emps c_emps%ROWTYPE;
l_returnvalue emp_t;
BEGIN
OPEN c_emps;
LOOP
FETCH c_emps
INTO r_emps;
l_returnvalue := emp_t(r_emps.empno, r_emps.ename, r_emps.job
,r_emps.mgr, r_emps.hiredate
,r_emps.sal, r_emps.comm);
EXIT WHEN c_emps%NOTFOUND;
PIPE ROW(l_returnvalue);
END LOOP;
CLOSE c_emps;
RETURN;
END get_operations;
—
BEGIN
NULL;
END emp_tf;
/
In the implementation of the functions I could of course use BULK OPERATIONS to speed up the processing instead of the row-by-row (or slow-by-slow) approach I used now, but using bulk operations use more memory (the retrieved data is loaded into memory) and that is exactly what I am trying to avoid. Using BULK COLLECT with a limit clause might be an option, but that is something to explore later on.
The new view looks like this:
create or replace view v_emps_tf as
select * from table(emp_tf.get_accounting)
union all
select * from table(emp_tf.get_research)
union all
select * from table(emp_tf.get_sales)
union all
select * from table(emp_tf.get_operations)
/
Pretty much the same as the original one, but now based on my functions.
Let’s take a look at the ‘strange’ things here. When you look at the function specification it says it will return a nested table type, but in fact it will return nothing. Every result gets piped out of the function (PIPE ROW) when it is done.
Another thing is the from part of the queries. We tell the SQL engine to treat the results of the function as if it were a table. For this we use the TABLE() operator.
Doing some checks (select * from v_emp minus select * from v_emp_tf) tells me the result of both views is the same, since there are no rows returned. When I turn timing on I notice that the ‘normal’ SQL approach is faster than the table function approach. But the issue wasn’t speed, it was memory usage.
How about the read consistency? PL/SQL functions in the query could be a trouble in the multiuser environment. The point is that the SQL statements starting at different times in the main SQL and via the PL/SQL functions, unless you use the “set transaction isolation level serializable “ pragma before the SQL statement will be used.
@Peter Rurenga: Yes, Table Functions can take parameters, that would make the PL/SQL code easier but this example was not to create the best PL/SQL code, but to display the idea behind Table Functions. You can even send in a cursor variable as a parameter.
@Jean-Marc Desvaux: The PL/SQL code is not considering the best practices. As you can see, there is not error-handling whatsoever in the code.
I had to convert my ‘real’ solution into an EMP-DEPT demo, so I removed as much extras as possible.
.. and what about this.. even shorter, and LIMIT of the inline cursor will be 100 automatically in 11g, so the amount of memory needed is “limited” :
CREATE TYPE emp_t AS OBJECT
(
empno   NUMBER(4),
ename   VARCHAR2(10),
job     VARCHAR2(9),
mgr     NUMBER(4),
hiredate DATE,
sal     NUMBER(7, 2),
comm    NUMBER(7, 2)
)
/
CREATE TYPE emps_tt AS TABLE OF emp_t
/
CREATE OR REPLACE PACKAGE get_data ISÂ
FUNCTION get_emp ( P_deptno varchar2 ) RETURN emps_tt PIPELINED;
END get_data;
/
CREATE OR REPLACE PACKAGE BODY get_data IS
FUNCTION get_emp ( P_deptno varchar2 ) RETURN emps_tt PIPELINED
IS
out_rec emp_t;
BEGIN
FOR i IN ( SELECT * FROM EMP WHERE DEPTNO = P_deptno )
LOOP
out_rec := emp_t( i.empno, i.ename, i.job, i.mgr, i.hiredate, i.sal, i.comm);
PIPE ROW(out_rec);
END LOOP;
RETURN;
END get_emp;
END get_data;
/
CREATE OR REPLACE VIEW v_emp_data
AS
SELECT * FROM TABLE (get_data.get_emp (10))
UNION ALL
SELECT * FROM TABLE (get_data.get_emp (20))
UNION ALL
SELECT * FROM TABLE (get_data.get_emp (30))
UNION ALL
SELECT * FROM TABLE (get_data.get_emp (40))
/
As for some very informative examples of flexible pipelined functions:
http://www.oracle-developer.net/display.php?id=420
To be sure of using bind variables instead of the literal values for department number, it’s even possible to embed the use of a context within the package AND view… like this:
create context ctx_get_data using ctx_mgmt;
create or replace package ctx_mgmt as
procedure set_value( P_context varchar2, P_attribute varchar2 , P_value varchar2);
end ctx_mgmt;
/
create or replace package body ctx_mgmt as
procedure set_value( P_context varchar2, P_attribute varchar2 , P_value varchar2) is
begin
dbms_session.set_context(P_context, P_attribute, P_value);
end set_value;
end ctx_mgmt;
/
CREATE OR REPLACE PACKAGE get_data_ctx ISÂ Â
TYPE r_cursor IS REF CURSOR return emp%rowtype;
FUNCTION get_emp ( P r_cursor, P_deptno varchar2 ) RETURN emps_tt PIPELINED;
END get_data_ctx;
/
CREATE OR REPLACE PACKAGE BODY get_data_ctx IS
FUNCTION get_emp ( P r_cursor, P_deptno varchar2 ) RETURN emps_tt PIPELINED
IS
out_rec emp_t;
this_rec P%rowtype;Â Â Â
BEGINÂ
ctx_mgmt.set_value(‘CTX_GET_DATA’, ‘DEPTNO’, P_deptno);
LOOP
FETCH P INTO this_rec;
EXIT WHEN P%NOTFOUND;
out_rec := emp_t( this_rec.empno, this_rec.ename, this_rec.job, this_rec.mgr, this_rec.hiredate, this_rec.sal, this_rec.comm);
PIPE ROW(out_rec);
END LOOP;
RETURN;
END get_emp;
END get_data_ctx;
/
CREATE OR REPLACE VIEW v_emp_data_ctx
AS
SELECT * FROM TABLE (get_data_ctx.get_emp (CURSOR (select * from emp where deptno= SYS_CONTEXT(‘CTX_GET_DATA’, ‘DEPTNO’)), 10))
UNION ALL
SELECT * FROM TABLE (get_data_ctx.get_emp (CURSOR (select * from emp where deptno= SYS_CONTEXT(‘CTX_GET_DATA’, ‘DEPTNO’)), 20))
UNION ALL
SELECT * FROM TABLE (get_data_ctx.get_emp (CURSOR (select * from emp where deptno= SYS_CONTEXT(‘CTX_GET_DATA’, ‘DEPTNO’)), 30))
UNION ALL
SELECT * FROM TABLE (get_data_ctx.get_emp (CURSOR (select * from emp where deptno= SYS_CONTEXT(‘CTX_GET_DATA’, ‘DEPTNO’)), 40))
/
This works because the actual cursor gets parsed within the begin – end block, and in this case after setting of the context.
Use of a context should make some difference in the time it takes to get all rows of the view, but I haven’t measured it yet.
Â
Â
Nice use of pipelined function. I suspect this is’t the real view ( you use the emp table as a demo ), so commenting on the very obvious great lack of memory or using a context for your where stat, are probably a bit off the mark.
I really like these pipelined functions, mainly because of the very fast showing of (first) results, but I also never encountered any issues with resource use ( cpu or memory) .
When I use a pipelined function, I tend to make it as flexible as possible, meaning that if it’s possible to use a parameter value, or even parameter cursor, I use that instead of hardcoded values or cursors.
Another version of your code is this one:
CREATE TYPE emp_t AS OBJECT
(
empno   NUMBER(4),
ename   VARCHAR2(10),
job     VARCHAR2(9),
mgr     NUMBER(4),
hiredate DATE,
sal     NUMBER(7, 2),
comm    NUMBER(7, 2)
)
/
CREATE TYPE emps_tt AS TABLE OF emp_t
/
CREATE OR REPLACE PACKAGE get_data ISÂ Â
TYPE r_cursor IS REF CURSOR return emp%rowtype;
FUNCTION get_emp ( P r_cursor ) RETURN emps_tt PIPELINED;
END get_data;
/
CREATE OR REPLACE PACKAGE BODY get_data IS
FUNCTION get_emp ( P r_cursor ) RETURN emps_tt PIPELINED
IS
out_rec emp_t;
this_rec P%rowtype;Â Â Â
BEGIN
LOOP
FETCH P INTO this_rec;
EXIT WHEN P%NOTFOUND;
out_rec := emp_t( this_rec.empno, this_rec.ename, this_rec.job, this_rec.mgr, this_rec.hiredate, this_rec.sal, this_rec.comm);
PIPE ROW(out_rec);
END LOOP;
RETURN;
END get_emp;
END get_data;
/
CREATE OR REPLACE VIEW v_emp_data
AS
SELECT * FROM TABLE (get_data.get_emp (CURSOR (SELECT * FROM emp WHERE deptno=10)))
UNION ALL
SELECT * FROM TABLE (get_data.get_emp (CURSOR (SELECT * FROM emp WHERE deptno=20)))
UNION ALL
SELECT * FROM TABLE (get_data.get_emp (CURSOR (SELECT * FROM emp WHERE deptno=30)))
UNION ALL
SELECT * FROM TABLE (get_data.get_emp (CURSOR (SELECT * FROM emp WHERE deptno=40)))
/
Â
It’s a bit shorter ( always good ), and by selecting all columns in the view, and selecting less within the object type, you win some flexibility.  If you want another set of columns, it’s just a matter of adding an extra set of object types ( Row and Table ), and adjusting the out_record within the package. You might even consider adding an extra parameter to the function, in order to be able to choose wich columns you want to show in your view.
Youre comments please…
Â
Hi Patrick, thanks for this good and adequate example of pipelined table functions.
One question: can pipelined functions take parameters? If so, there is no need for a different function for every department in EMP.
The view could then be something like
create or replace view v_emps_tf as select * from table(emp_tf.get_dept(accounting))
union all
select * from table(emp_tf.get_dept(research)) etcetera
Patrick,
Thanks for this interesting post.
You may consider handling the NO_DATA_NEEDED exception raised by pipelined table functions, specially if in some cases you want to select from the v_emps_ts view with a where clause.
Jean-Marc
Patrick,
In case you select from the view with a WHERE clause, restricting the piped records it seems your emp_tf.get_x functions cursors will not get closed properly. To make it clean you need to handle the NO_DATA_NEEDED exception raised by pipelined functions.
Jean-Marc