Laltely 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

Source Article from

It’s been a while since I posted my last blog. I’v been busy in a new job as Apex coach for a large dutch bank. The department I work for used to be an Oracle Designer and Forms shop, but last year they discovered the possibilities of Apex by a proof of concept application.
I have held several presentations for my collegues about Apex. They were impressed by the ease of application development, but they missed some features they use in Oracle Forms. One of them is spread tables. When you have a very large form, a spread table gives the the opportunity to scroll through a table both horizontally and vertically, while the heading and the row context stay in place.
Standard Apex provides you with normal HTML tables. There is no such functionality as spread tables possible, is it…?

When I was googling the internet on this subject I came across this site describing a technique to use spread tables using CSS and a bit of Javascript. It is possible to lock one or more columns to keep the context of the rows visible.
Great solution! The only drawback is that it only functions in IE and from version 5 upward, but the bank I work with has standardised on IE for browsing. As long as this feature is used within the company there is no problem.

But I want to use it in Apex. My idea is to create a new Report Template, that can be used to create spread table reports just like you create normal reports.

I have based my new Report Template on the Alternating colors template. In the Before Rows section a DIV container is started, and the table is assigned the ID spreadtable:

which is ended in the After Rows sec

The format of the tablecells is controlled by the CSS. The class locked is assigned to table cells to be locked. The locking of the column headers to the top border of the div is done by :

As you see the top position of the header cells is determined by a Javascript expression, which can also contain a custom Javascript function. The expression keyword in this syntax is only evaluated by Internet Explorer, it will not work in other browsers.
The column cells that serve as row context are locked to the left side of the container with the folowing CSS.

The full CSS can be found here
The fact that it is based on an alternating colors table makes the CSS more complicated. Apart from this to obtain a clear separation between scrollable and locked columns I needed to create three new classes xxx_last, having a grey right border.

Next we need some Javascript to assign width and height to restrain the table container and to actually assign the class locked to the table cells we want to lock. For a start we put this code in the pageheader.

function lockColumns(regionDivID, width, height, noOfColumns)
{ // set DIV properties
div = scrollDiv(regionDivID); = width; = height; = "auto";
// look for table
var tables = div.getElementsByTagName('TABLE');
var table = tables[0];
var tableRows = table.getElementsByTagName('TR'); //collection of rows
var className = 'locked';
for (i = 0; i { var tableRow = tableRows.item(i);
for (j=0; j { if (j == noOfColumns - 1 ) { className = className + "_last"; }
tableRow.cells[j].className = className; // last column get grey right border
if (className == 'locked_last')
{ className = 'lockedalt'; }
{ className = 'locked'; }

We call this funtion in a HTML region that we put behind the reports region so that we are sure that the region has rendered already.

lockColumns ( "ST_REGION", "400px", "250px", 2);

With this call we restrain the report in region with ID ST_REGION to a size of 400×250 px. A row context of 2 columns remains locked in position.

The result is shown on this page – remember it only functions with IE 5 and up. In this page support is added for Ajax pagination. In the Reports template the pagination links are supplied with a call to the lockCols procedure:

I am still working on adding this call to the sort-links in the column headers.

Happy Apexing!

Source Article from