Monday, 21 July 2008

Automatic generation of Oracle pipelined functions

Lately I have done some projects that needed the implementation of Oracle pipelined functions. You would typically use pipelined functions for performance or flexibility (for example when using dynamic SQL).
It works great once you have got the job done, but before that there is a lot of typing involved. I myself always have to Google for an example of which objects to create and how it should be done. Then at least an hour further I have got my pipelined function running. This, as a real developer, made me wonder whether is would be possible to automate this process. And after some hours of thinking, I found a solution.

Each pipelined function is based on a table like structure, so we can start with a table or a view, say the known table DEPT. With the definition of this table we can build the rowtype object.

create or replace type dept_rowtype
as object
( deptno number(2,0)
, dname varchar2(14)
, loc varchar2(13)

On the rowtype object we need to define a tabletype object, that easy (and short):

create or replace type dept_table_type as table of dept_rowtype;

Then we need to write our function, that is declared returning the tabletype by in fact returning a lot of rowtypes. This is, even for a small table like DEPT quite a lot of typing.

create or replace function dept_pf return dept_table_type pipelined is
cursor c_cur is
select deptno
, dname
, loc
from dept;
r_rec dept_rowtype;
l_deptno number(2,0) := null;
l_dname varchar2(14) := null;
l_loc varchar2(13) := null;
open c_cur;
fetch c_cur into l_deptno
, l_dname
, l_loc
exit when c_cur%notfound;
r_rec := new dept_rowtype ( l_deptno
, l_dname
, l_loc
pipe row (r_rec);
end loop;

At last we need a view to show the data:

create or replace view dept_view as select * from table(dept_pf());

This code is all be generated automatically on the basis of a table definition. You can reach the Apex application to generate all this code with this button:

It's really easy and fast!
Enter the name of the table, which is used to generate the names of the other objects. Then go to the Object Browser and download the column definitions. Cut the Name and Datatype columns in Excel and Paste them in Column Definition. Now hit the Generate button and presto you have got your scripts. Run them in SQL*Plus or TOAD and you have got your pipelined function working within a few minutes. Now you can query the view based on the function.
Off course you would like to perform some changes on the function itself, because you did not do all this just to get the same result as querying the table directly ;-).
But you did not have to do all the typing,

Hope you enjoy the generator, let me know what you think of it!

Dick Dral

PS A new version of the generator is available here


Keith Jamieson said...

Oracle 11g
When I generate a pipelined function form user_objects view
I get the following:

create or replace function user_objects_pf return user_objects_table_type pipelined is
cursor c_cur is
from user_objects;

What exactly should the column format look like in the columns box.

I do like the idea

Dick Dral said...


the colum format in de columns box should contain the column name followed by the datatype.
I have included an example in the application.

Thanks for the feedback.