🚀 go-pugleaf

This is a multi-part message in MIME format.
--------------0D86AE2A413914B988FC1055
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

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:

SOLUTION 1:
-- 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.

SOLUTION 2:
-- 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
parameter.
-- 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" <avek_nospam@videotron.ca> wrote in message news:<457W8.6770$Qt6.589048@wagner.videotron.net>...
> > 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

--------------0D86AE2A413914B988FC1055
Content-Type: text/x-vcard; charset=UTF-8;
 name="martin.doherty.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Martin Doherty
Content-Disposition: attachment;
 filename="martin.doherty.vcf"

begin:vcard 
n:Doherty;Martin
tel;work:+1 650 506-8398
x-mozilla-html:FALSE
url:http://industries.oraclecorp.com
org:Oracle Corporation;Industry Solutions
version:2.1
email;internet:martin.doherty@oracle.com
title:Senior Software Specialist
adr;quoted-printable:;;Mail Stop C3038=0D=0A10 Twin Dolphin Dr=0D=0A;Redwood Shores;CA;94065;USA
fn:Martin Doherty
end:vcard

--------------0D86AE2A413914B988FC1055--