Sounds like a good use of a GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE
ROWS (that is, data survives commits and is only disposed of when Oracle session
ends.) You kill both rabbits with it: different sessions see their own data only,
and cleanup is done automatically for you (and almost no redo is generated), no
need to rollback, no need for session id column... The only issue I can see here is
connection pooling - in this case you would need to truncate the temp table first
thing before populating it for new request, because Oracle session never ends and
will see data generated by previous call. If connection pooling is not used (and
is not planned), then you can safely use GTT as is. Using permanent tables for
temporary storage is only sound when you work in a stateless environment (like
web application, for example, where state between calls is not maintained, so you
have to preserve it yourself.)
Corrections and additions welcome.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Martin Doherty" <martin.doherty_at_oracle.com> wrote in message news:3D2B2142.EC54B769_at_oracle.com...
> 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_at_videotron.ca> wrote in message
news:<457W8.6770$Qt6.589048_at_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
>
Received on Tue Jul 09 2002 - 14:03:32 CDT
