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

ramon_at_conexus.net (Ramon F Herrera) 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

You may want to look at global temporary tables. They can be accessed by the same name by many sessions, yet data there will be private to each session. After session completes, data is gone. Using PL/SQL tables (similair to an array)is also a possibility. You can create a function which will populate such a table with results of some query and then return this table. With a little trick you can even select from this function.

    Hth, Igor Izvekov. Received on Tue Jul 09 2002 - 16:01:17 CDT