Re: Temp tables

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 10 Mar 1999 15:11:18 GMT
Message-ID: <36e78b8e.8665920@192.86.155.100>

A copy of this was sent to "Sudeep Siaj" <sudeep4_at_excite.com> (if that email address didn't require changing) On Tue, 9 Mar 1999 11:29:05 -0700, you wrote:

>Does Oracle support temp tables?
>
>For example, on certain servers, I can say:
>
>SELECT *
>INTO #myTempTable
>FROM EMP
>
>This creates a temporary table named #myTempTable that is automatically
>dropped when the session terminates.
>
>Does Oracle support something like this?
>
>Thanks in advance!
>

Pure, ram based temp tables would look like the following in Oracle8:

SQL> create or replace type myScalartype as object   2 ( ename varchar2(25), empno int );   3 /
Type created.

SQL> create or replace type myTableType as table of myScalarType   2 /
Type created.

SQL> create or replace package my_pkg
  2 as

  3          function my_function_as_a_table return myTableType;
  4          pragma restrict_references(my_function_as_a_table,wnds,rnds,wnps);
  5  
  5          procedure init_table;
  6          procedure init_table2;
  7  
  7          pragma restrict_references(my_pkg,wnds,rnds,wnps,rnps);

  8 end;
  9 /
Package created.

SQL> create or replace package body my_pkg   2 as
  3
  3 tmp_table myTableType := myTableType();   4
  4 function my_function_as_a_table return myTableType   5 is
  6 begin
  7 return tmp_table;
  8 end;
  9
  9
  9 procedure init_table
 10 is
 11 begin

 12          tmp_table.extend;
 13          tmp_table(1) := myScalarType( 'Hello', 1 );
 14          tmp_table.extend;
 15          tmp_table(2) := myScalarType( 'World', 2 );

 16 end;
 17
 17 procedure init_table2
 18 is
 19 begin

 20      select cast( multiset( select ename, empno from emp where rownum < 6 )
 21                             as myTabletype )
 22        into tmp_table
 23        from dual;

 24 end;
 25
 25 end my_pkg;
 26 /
Package body created.

SQL> rem shows how to fill the temp table procedurally using your own logic SQL> exec my_pkg.init_table
PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2 from the(select cast(my_pkg.my_function_as_a_table() as myTableType)

  3                           from dual)

  4 /

ENAME                          EMPNO
------------------------- ----------
Hello                              1
World                              2

SQL> rem shows how to fill the temp table with the result of a query SQL> exec my_pkg.init_table2
PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2 from the(select cast(my_pkg.my_function_as_a_table() as myTableType)

  3                           from dual)

  4 /

ENAME                          EMPNO
------------------------- ----------
SMITH                           7369
ALLEN                           7499
WARD                            7521
JONES                           7566
MARTIN                          7654

In Oracle8i, release 8.1, there are disk based temporary tables as well. They support temporary result sets either transactionally (commit and the temp tables empty themselves) or session based (data put in a temp table stays there until you log out or delete it)...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Mar 10 1999 - 09:11:18 CST