Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Temporary Tables ?
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;
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;
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;
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
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