Re: Temp tables??

Answers embedded.

Hth,

Sybrand Bakker, Oracle DBA

"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.

In 8i, you can use dynamic sql with cursors. create a cursor variable using a ref cursor type and use open cv for '<any dynamic statement>'
You can also use bind variables if the bindvariables are in the string as :bindvar, you should just add the using clause to the open for.

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

Oracle 8i does have temporary tables. I haven't used them yet so I have to refer you to the pl/sql documentation.
If you don't have that, it's online at http://technet.oracle.com

> I'm stuck. Any Ideas??
>
> ____________________________
> Rob Gravereaux
> Web Developer
> Medical Broadcasting Company
> http://www.mbcnet.com
>
>
Received on Wed Oct 04 2000 - 15:18:11 CDT