Re: Temp tables??

8i does have temp tables (private to the session, no dml locks, dropped at the end of the transaction or the end of the session -you choose) -'create global temporary table X blah blah blah'

On the other hand, although it's not exactly nice, what's to stop your code creating a table, populating it, and then dropping it. Works for all known versions of Oracle -although it's not private to the session, there are dml locks, and your code has to remember to drop the table at the end of its shenanigans. Still, I always thought that was what the 'Tools' tablespace was for.

Regards
HJR "Rob Gravereaux" <rgravereaux_at_mbcnet.com> wrote in message news:39db7fd8$0$28308_at_wodc7nh0.news.uu.net...
> Does PL/SQL have something similar to SQL Servers Temp table
 functionality?
>
> I'm attempting to create a PL/SQL SP (Oracle 8.1.6) to create and return a
> cursor to the client (ado 2.1 SP2 w/Oracle provider). The records can't
 be
> retrieved with a simple SQL statement - The code needs to 'walk' up a
> self-referenced table and do some weird comparisons to find the correct
> rows. I believe I have 2 options (for PL/SQL anyway):
>
> 1) Create a cursor with criteria for specific rows. This would then
 involve
> creating the cursor with some dynamic SQL ("Select * from table where key
> in(1, 5, 12, ...)") but AFAIK cursors cannot be created with dynamic SQL -
> it's compiled with replacement params only.
>
> 2) When found, write records to a temp table, then do a select on the temp
> table. But I don't think Oracle has a temp table concept - like SQL
 Servers
> #temptable stuff.
>
> I'm stuck. Any Ideas??
>
> ____________________________
> Rob Gravereaux
> Web Developer
> Medical Broadcasting Company
> http://www.mbcnet.com
>
>
Received on Thu Oct 05 2000 - 06:26:21 CDT