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

ramon_at_conexus.net (Ramon F Herrera) writes:

> "AV" <avek_nospam_at_videotron.ca> wrote in message news:<457W8.6770$Qt6.589048_at_wagner.videotron.net>...

> 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?
>

Maybe CREATE GLOBAL TEMPORARY TABLE will do what you want. I believe this is available from 8i upwards. The table has two options - deletion of data on commit or persistance of data on commit (but only until the end of the current session). This avoids having to remember to delete the data from the temporary table before you next use it. Also, other sessions/users cannot see the data in the table, though they can use the table if given the right permissions. This would imply that you don't need to worry about different sessions/users getting data mixed together etc. I also think that because it is put into the temporary table space you will avoid disk fragmentation plus I (*think* redo logs etc are not maintained on these temp tables, so they will probably be faster.

Tim

--