Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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: AV <avek_nospam_at_videotron.ca>
Date: Tue, 9 Jul 2002 09:45:28 -0400
Message-ID: <SNBW8.29157$Qj4.1686313@weber.videotron.net>

"Ramon F Herrera" <ramon_at_conexus.net> wrote in message news:c9bc36ff.0207081836.29106794_at_posting.google.com...
> "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

Ramon,

there is in-memory "PL/SQL TABLE" object in Oracle. Handling of such tables is a bit tricky but not rocket science.

Alternative would be exclusive locking of TempGUI table.

Alternative would be synchronizing java code assessing this pl/sql code. This is valid ONLY if there is no other way this stored procedure is called.

AlexV. Received on Tue Jul 09 2002 - 08:45:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US