Re: Does Oracle 8 Support Temp Tables?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 06 Mar 1999 16:56:09 GMT
Message-ID: <36eb5d82.11001439@192.86.155.100>

A copy of this was sent to Scott Dudley <scottd_at_goodnet.com> (if that email address didn't require changing) On Sat, 06 Mar 1999 16:22:00 +0000, you wrote:

>
>I'm new to Oracle and have developed an Oracle 8 Pro*C application. I'm
>now optimizing for performance and was wondering if Oracle 8 allows for
>creation of temp tables/indexes in RAM as other DBMS' do. If so, would
>someone enlighten me as to syntax, etc. as I have limited access to
>documentation?
>
>Many thanks.

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 Sat Mar 06 1999 - 10:56:09 CST