Re: Sybase vs Oracle: Temp Tables

On 12 Aug 1998 11:37:03 -0500, arenson_at_swan.imgen.bcm.tmc.edu (Andrew D. Arenson) wrote:

>
> My first question about differences between Sybase and Oracle
>in terms of bcp vs SQL*Loader got such a great response, that I'm going
>to try with a second question.
>
> In Sybase, I'm used to creating temporary tables on the fly
>without having to run the 'create table' command, by using the
>following syntax:
>
> select ...
> into #TMP_TABLE
> from ...
> where ...
>
>This doesn't work in Oracle (as far as I know). In fact, the
>'SELECT ... INTO' syntax appears to be used for some sort of single
>row loading of data into variables that I haven't fully explored yet.
>
>So, I'm left with a two-part question:
>
> 1) Does Oracle allow one to create a table as the result
> of a select statement without having used the 'create table'
> command?
>
> 2) If so, does Oracle automatically clean up this dynamically
> created table at the end of the session, like Sybase does?
>
>
>Thank you in advance for any responses!

Oracle has no concept of a user defined / created temporary table. This is pathetic, but something we live with. To get around it, you can use the

        create mytemptable as selct * from ...

syntax, to create a normal table. The drawback is you have to explicitly drop it when you're done.

Oracle is the only major RDBMS that doesn't have this. (Sybase, MS SQL Server, Informix, DB2 all have it)

You could always put in an enhancement request ... sorry, bad joke.

Ciao
Fuzzy
:-) Received on Wed Aug 12 1998 - 19:11:47 CDT