Re: Oracle replacement for Sybase #temp tables

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 - 13:49:50 CST