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
