Re: Oracle replacement for Sybase #temp tables

Tom,

    Just to make sure I undertand your suggestion. Are you recommending that a pool of names be used ranging from temp_0001 - tempnnnn? The only problem with this is that we may want to track the owner of a temp table to a session. That's why the idea of tacking on the session_id to the table name is appealing. If we just hand out numbered tables I'm not sure how we could track the owner for troubleshooting. I'm new to Oracle so let me know if I'm missing something here.

Your suggestion for clean up makes sense.

Thanks,
Kristy

Tom Best wrote:

> You could use a sequence generator to create the temp table. Call it
> temp_nnnnn or something. If you need a cleanup routine, it may be easiest to
> query the system catalog of all tables starting with "temp_" and drop each one
> in a loop.
>
> You could also use a random number generator. We use this, only because we
> had to be compatible with about 8 different DBMSs.
>
> Tom Best
>
> Kristy Kallback-Rose wrote:
>
> > We are in the process of converting to Oracle from Sybase. Under Sybase we
> > had used #temp tables quite a bit (within SQRs, and in the SQL for
> > datagroup builds to avoid complex joins, for example) and need a
> > replacement for them in Oracle.
> >
> > Oracle's global temporary tables aren't quite ready for prime-time yet, in
> > that they have some optimizer problems. I don't want to start a discussion
> > about GTTs, we've already decided not to use them. Rather I'm hoping to
> > hear from someone who used Sybases's #temp tables and is now using, for
> > lack of a better term, 'permanent temporary tables' in Oracle with
> > something like sessionid key to keep the table name unique. It is a
> > requirement, at least for the SQRs, that the table name be unique.
> >
> > If someone does have this type of experience behind them I'd be glad to
> > provide more details. You can reply to this post or via e-mail.
> >
> > Thanks,
> > Kristy
> > --
> > Kristy Kallback-Rose
> > Information Access & Repository Services Team
> > University Information Technology Services
> > Indiana University, Bloomington
Received on Tue Jan 04 2000 - 14:31:46 CST