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;
 begin 
    open c_cur; 
   loop 
 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; 
   return; 
 end; 
/

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