Re: Temporary Tables ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 04 Jan 1999 17:01:33 GMT
Message-ID: <3697ef12.11716617@192.86.155.100>

A copy of this was sent to Matthew Green <matthewgreen_at_powersoft-services.co.uk> (if that email address didn't require changing) On Mon, 04 Jan 1999 14:48:46 +0000, you wrote:

>Is it possible to create temporary tables within a connection
>
>and if so how ??
>
>thanks
>
>Matt
>

In 7.x, no, there are no temporary tables. You must use a 'permanent' table

In 8.0, yes, you can do in memory temporary tables. For example:

create or replace type myScalartype as object ( ename varchar2(25), empno int );
/
create or replace type myTableType as table of myScalarType /

the above creates the structure of your temporary table. My example is a 2 column temporary table.

declare

    tmp_table myTableType;
begin

    select CAST( multiset( select ename, empno

                             from emp where rownum < 6 ) AS myTableType )
      INTO tmp_table
      from dual;


    for x in ( select *
                 from THE(select cast(tmp_table as myTableType) from dual) )
    loop
        dbms_output.put_line( x.ename || ' ' || x.empno );

    end loop;
end;
/

the above shows how to populate a temporary table from a select and then fetch from it. Lets say you need 'session persistence', that is the table will stay in memory for the duration of the session. Then, all you need to do is put the 'tmp_table' variable into a package spec and it will be session persistent. If you want to select from the package variable in 'regular' sql, you'll need to add a function to that 'gets' it. For example

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  
  6          pragma restrict_references(my_pkg,wnds,rnds,wnps,rnps);

  7 end;
  8 /
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 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 end my_pkg;
 18 /
Package body created.

SQL> exec my_pkg.init_table
PL/SQL procedure successfully completed.

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

In Oracle8i, disk based temporary tables are introduced as well....  

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  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jan 04 1999 - 11:01:33 CST