Re: Temporary tables

javier (mendillo_at_worldonline.es) wrote:

: De: javier <mendillo_at_worldonline.es>
: Asunto: Temporary tables.
: Fecha: lunes 30 de octubre de 2000 19:38
 

: Hello all,
 

: We need to create temporary tables. Is it posible in oracle database?
: How?

CREATE TABLE xyz ...

then later

DROP TABLE xyz ...

This makes a table specific to the username, which is fine if the user is only logged in once at a time. If you do this from SQLPlus then you can preassign a DEFINE variable to make the table unique, one technique for SQLplus, assuming the session has a unique place to SPOOL into

	SQL> SPOOL MY_TEMP.SQL
	SQL> select 'DEFINE X='||userenv('SESSIONID') from dual ;
	SQL> SPOOL OFF
	SQL> @my_temp
	SQL> CREATE TABLE XYZ_&X (... etc...)
	SQL> INSERT INTO XYZ_&X VALUES ( ...ect...)

You can also keep a single "working" table indexed by SESSIONID and/or USER to allow multiple processes to access the same table as if it were their own, perhaps using a VIEW and/or triggers so each session sees the table with only their own data.

The latest Oracle releases also has a command

        CREATE TEMPORARY TABLE which looks useful, though I haven't used it. Received on Wed Nov 01 2000 - 13:04:35 CST