Re: 'temp' oracle tables

nuday_at_usa.net wrote:
>
> Gopal wrote:
> >
> > Hi Guys,
> >
> > We are in the middle of porting a rather complex
> > SQL Server app. to Oracle 7.3.x.
> >
> > I confess at the outset that I am new to Oracle.
> >
> > I would like to find out about how to create and user 'temp' tables
> > in Oracle in a manner similar to SQL Server. In SQL Server
> > it is possible to create temp tables in a stored procedure and this
> > table is only visible to that procedure and dies after the
> > procedure completes.
> >
> > Thank You
> >
> > Regards
> >
> > Gopal
> > please email to gopal_at_astro.ocis.temple.edu
>
> The reason as to why you want to create "temp" tables is not clear from
> your posting. However I would like to mention that you could possibly
> use PL/SQL tables which remain local to the procedure.
> The other possible option would be to create "temp" tables using dynamic
> sql and then drop them in the procedure. Remember however, that
> such statements cause an implicit COMMIT to be issued.

ORACLE does not support #temp tables, any table that you create using dynamic SQL will be visible to all the users. The only solution that I found was to create a permanent table to match the desired #temp table and to add an "ID" column to the table - you can generate a unique ID for each session using an ORACLE sequence.

BTW, ORACLE CAN NOT return SQLServer-style result sets from stored procedures.

-- 
____________________________________
Oleg Dimerman (olegd_at_e-mail.com)