Kevin,
Temp tables are vastly different in Oracle.

First off, you can't create one on the fly. To create temp tables you need to execute Data Definition Language (DDL) commands. To do this on the fly, you need to use dynamic SQL, either via the DBMS_SQL package (<8i) or EXECUTE IMMEDIATE (>= 8i). However, functions/SPs/packages are compiled and Oracle checks to make sure all objects exist at compilation time (not just at run-time). If you create something on the fly and have code to access it later, the PL/SQL block will fail when compiled because the object won't exist yet.

To get around this, you need to create temp tables as part of your database setup when you create all your other tables. The syntax is something like:

create global temporary table #TEMPGEO (etc.)

The nice thing about temp tables in Oracle is that everyone can see the structure of the table but the data is bound the Oracle connection that created it. This may present a problem if you are accessing the server via a middle-tier object (like we are). To get around this, I perform a Truncate Table before the SELECT ... INTO.

And yes you can perform joins, subqueries, etc. on temp tables and the syntax is the same as if the table were normal.

HTH.

>I'm trying to determine if our use of #TEMP tables in SQL Server 2000 will port to Oracle.
>
>For instance, in SQL Server 2000 we do things like the following:
>
>1) When a user runs a report where they can select a variable # of accounts and products, we will create 2 temp tables (#TEMPGEO and #TEMPPROD), containing the geo/prod keys that the user selected. We'll then run a query against the history table, joining #TEMPGEO and #TEMPROD. [All in one connection, obviously]. Can this approach be used in Oracle (both generally and with the same syntax]
>
>2) Related question...in SQL Server, I can do this...
>
>SELECT Invoice.* INTO #TempInvoices JOIN WHERE
>
>And then I can do a subsequent query against #TEMPINVOICES. I can even do this with SQL pass-through...again, can this be done in Oracle with the same syntax?
>
>I guess my general question is whether this is part of the ANSI-92 standard, or if it's just something that's portable to Oracle...or are there minor differences in syntax.
>
>Thanks,
>Kevin

Larry Miller
MCSD
LWMiller3@verizon.net

Accumulate learning by study, understand what you learn by questioning. -- Mingjiao