Re: Temp tables??

Creating and Dropping a table every time you do a query is Very expensive. Rather then that, create a permanent table that has all of the columns needed and an extra Key column. The proc would populate the table with the answers and add a unique key to the Key column. Pass back to the client the key value. Then the client can do a simple Select * from PermTemp_table where key = v_key. The client can call another proc to clean out the data when no longer needed.

In article <39dc57e4$1_at_news.iprimus.com.au>,   "Howard J. Rogers" <howardjr_at_www.com> wrote:
> 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
> >
> >
>
>

--
Programming is easy, solutions are hard.


Sent via Deja.com http://www.deja.com/
Before you buy.