A practical example of using global temporary tables within Apex: displaying dbms_output messages

It's late so i am not going to make it longer than necessary.

Some days ago i read an interesting question in the OTN forum and i threw my 50 cent advice (well, honestly it may have been worth 60 or 70 cents as i did some homework before shooting...).

In short, a user was asking for a method for display the messages sent to the DBMS_OUTPUT buffer.

This question eventually aroused my fantasy, so i wrote a generic function for storing dbms_output messages into a generic table (a table in the same schema or in another one, provided the user has INSERT privilege).
Having used up all my fantasy in the development, i decided to call the function STORE_DBMS_OUTPUT.

In my test apex application the staging table is defined as follows:

create global temporary table
staging_table(
id number(5,0),
text varchar2(255)
) on commit delete rows
/

The source code of the function can be downloaded from Yocoya.com.

The function takes 4 parameters and returns a number:

p_owner owner of the staging table, default to current user.
p_table name of the table, mandatory
p_txt_col name of the column for storing messages, typically VARCHAR2(255)
p_cnt_col optional name of the column for storing message sequence number

The last parameter may be omitted if you already have a before-insert trigger on the table where you stuff an oracle sequence.

The function retrieves all the content in the buffer and the value it returns is the number of messages it found.

Although i wrote the function for using within an Apex page, it's completely independent from it and it can be used in different situations.
The table where the messages are going to be stored doesn't necessarily have to be a Global Temporary Table (GTT), although in that case you must devise a method for keeping it clean without interfering with other sessions and/or users.

Users who are interested in looking at a working example of dynamic SQL in combination with FORALL and bulk binds may be interested in this function too.
Note also the n-tier usage of DUAL i talked about some time ago that i use here for initializing a pl/sql table containing the subscripts, i'll probably write something in another posting on this subject.

In case you are interested in using this function inside apex, here is how i did it (please note that this techniques are still under testing).

before header processes
step 10: truncate staging table (just in case) and call the desired procedure returning dbms_output messages (conditional on request=OUTPUT)
step 20: get the dbms_output and store it in a global temporary table (conditional on request=OUTPUT), as follows:

declare
n integer;
begin
n := STORE_DBMS_OUTPUT(
p_owner => :OWNER,
p_table => 'STAGING_TABLE',
p_txt_col => 'TEXT',
p_cnt_col => 'ID');
end;

regions
sequence 10: some region with a button branching to the same page, setting request = OUTPUT
sequence 20: report region on staging table (conditional on request=OUTPUT)
sequence 30: PL/SQL type region truncating the table (conditional on request=OUTPUT), may be omitted altogether if GTT is defined as on commit delete rows.

As you see here i have up to two truncates statements. This is just to avoid having an idle session holding some temporary segment as i related in my previous posting.
If the GTT is defined as on commit delete rows, you can probably omit both truncates, i just prefer to leave a clean table after using it.

That's it.