Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: How to aggregate PL/SQL records into a Result Set?

Re: How to aggregate PL/SQL records into a Result Set?

From: Martin Doherty <>
Date: Tue, 09 Jul 2002 10:45:38 -0700
Message-ID: <>

This rings a bell ... I solved several similar problems waaay back in 1998 with Oracle Reports 2.5 (can't remember server version but probably 8.x).

I created a permanent table to hold temporary data. The report would first call a PL/SQL stored procedure from a report trigger, which performed some complex query processing and populate the summary data into the temp table, then the report data model would contain a fairly simple set of queries to fetch from the temp table and produce output.

Concurrency was an issue - two users could run the report concurrently, and both reports would populate their own temp data into the table, which needed to be kept separate.

If the pre-processing is not too complex, the whole thing could be done inside one logical transaction:


-- Report starts up, connects to the database.
-- Trigger calls stored procedure
-- Stored procedure (which executes within same session and transaction) inserts rows into temp table,
but does not commit.
-- Report data model queries all rows in table (since no rows are ever committed, only this user's current pending rows are returned).
-- Report issues a ROLLBACK and exits.

This was fine if the volume of inserted rows was low, all rows were uncommitted and so the table was always "empty" and didn't need to be purged.

However, if the preprocessing gets complicated you want to be able to query the inserted rows for debugging, so it becomes necessary to commit the rows.


-- Report starts up, connects to database.
-- Trigger queries database to get current session id (sorry I forgot exactly how this was done)
-- Trigger calls stored procedure and passes session id.
-- Stored procedure inserts rows into temp table, and commits. Every row inserted contains the session id
-- Report data model queries only the rows in table WHERE SESSION_ID = :p_session_id (so only this session's rows are returned).
-- Report deletes all rows WHERE SESSION_ID = :p_session_id, and commits. (this was commented out during debugging).
-- Temp table might need occasional purging, due to program aborts. You could avoid this by timestamping each row, and start your program by deleting all rows more than X days old.

You are calling your stored procedure from java, but it sounds quite close. If your pl/sql call executes in the same transaction as your java selects, you can use solution 1. Regardless, you can use solution 2.

HTH Martin Doherty

Ramon F Herrera wrote:

> "AV" <> wrote in message news:<457W8.6770$>...
> > Most probably it will be somewhat slower,
> > but here is a plan:
> > -- create buffer table.
> > -- create PL/SQL procedure that
> > ---- clean buffer table
> > ---- populate table from any number of selects
> > -- from java code
> > -- call pl/sql procedure
> > -- call simplest select from buffer table
> >
> Alex:
> Right after I posted my question, I also came up with
> exactly the same solution that you suggest (hey, great minds
> think alike ;-) ).
> My PL/SQL script uses a table called 'tempGUI' which is populated
> one record at a time, and its contents are removed after being sent
> to the client (actually, the deleting the old table is the very
> first thing done). The obvious problem is name collision.
> What if two clients access the stored procedure at the same time?
> Is there any way to have a private table (visible by only one
> instance of the stored procedure)? Or even better, is there some
> sort of RAM-based table?
> BTW: I intend to implement the PIPELINED approach as soon as I have
> Oracle 9i up and running, but for now I am using the approach
> that you suggested. Again, I am worried about simultaneous access.
> Thanks,
> -Ramon

Received on Tue Jul 09 2002 - 12:45:38 CDT

Original text of this message