Re: Temp table question

From: Bob Schmitz <bschmitz_at_execpc.com>
Date: Fri, 2 Mar 2001 11:26:37 -0600
Message-ID: <fIQn6.3719$7e6.1469643@homer.alpha.net>

Oracle's implementation of the TEMP table is much different than Microsoft or Sybase. Because Sybase was the originator of this concept Microsoft has kept the functionality around. With Sybase, the TEMP table can be created explicitly using the SQL command "CREATE TABLE".

Tables that can be shared among Adaptive Server sessions via CREATE TABLE command:

                create table tempdb..authors (au_id char(11))
                go

        Adaptive Server does not change the names of temporary tables
created this way. The table exists until the current session ends or until
its owner drops it             using "drop table".

Tables that are accessible only by the current Adaptive Server session or procedure

                create table #authors (au_id char (11))
                go

        The table exists until the current session or procedure ends, or

until its owner drops it using "drop table"

For Oracle, temporary tables were added with Oracle8i. There are two types of temporary tables, GLOBAL TEMPORARY and TEMPORARY.

GLOBAL TEMPORARY table is one whose data is visible to all sessions
TEMPORARY table has contents only visible to the session that is using it

A temporary table can have session-specific or transaction specific data depending on how the ON COMMIT clause is used in the table's definition. The temporary table doesn't go away when the session or sessions are finished with it; however, the data in the table is removed.

Here is an example creation of both a preserved and deleted temporary table:

SQL> CREATE GLOBAL TEMPORARY TABLE test6 (

  2     starttestdate DATE,
  3     endtestdate DATE,
  4     results NUMBER)

5* ON COMMIT PRESERVE ROWS
SQL> / Hope this helps ...

-rws
"Van Messner" <vmessner_at_bestweb.net> wrote in message news:cbCn6.2835$Hg.222549_at_monger.newsread.com...
> Oracle now has global temporary tables. They're a big plus. Read about
> them in the documentation.
>
> Van
>
>
> "joebob" <joebob_at_zipcon.n0t> wrote in message
> news:983492427.437049_at_news.zipcon.net...
> > With SQL Sever I've created temp table structures in memory but am not
sure how to do this with Oracle 8.0.5. Is refcursor the
> > closest thing there is? Thank you
> >
> > --
> > To reply directly, replace the zero in my email address with the letter
e.
> >
> >
> >
>
>
Received on Fri Mar 02 2001 - 11:26:37 CST