I believe you mean "temporary tables" -- temporal tables in a database are another thing entirely (there is actually such a thing -- temporal tables are tables that can return the answer that existed at a point in time -- you can ask the table to return the answer that existed at midnight last night, instead of the answer that exists right now)...
Oracle's temporary tables are similar to temp tables in those
other databases the main exception being that they are 'statically' defined. You create them once per database, not once per stored procedure in the database. They always exist but appear empty until you put data in them. They may be SESSION based (data survives a commit but not a disconnect/reconnect). They may be TRANSACTION based (data disappears after a commit). Here is an example showing the behaviour of both. I used the scott.emp table as a template:
SQL> create global temporary table temp_table_session
2 on commit preserve rows
3 as
4 select * from scott.emp where 1=0
5 /
Table created.
the ON COMMIT PRESERVE ROWS makes this a session based temporary table. rows will stay in this table until a logoff. Only I can see them though, no other session will ever see 'my' rows even after I commit
SQL>
SQL>
SQL> create global temporary table temp_table_transaction
2 on commit delete rows
3 as
4 select * from scott.emp where 1=0
5 /
Table created.
the ON COMMIT DELETE ROWS makes this a transaction based temp table. when you commit -- the rows disappear.
SQL> insert into temp_table_session select * from scott.emp;
14 rows created.
SQL> insert into temp_table_transaction select * from temp_table_session;
14 rows created.
we've just put 14 rows into each temp table and this shows we can 'see' them:
SQL> select count(*) from temp_table_session
2 /
COUNT(*)
----------
14
SQL> select count(*) from temp_table_transaction
2 /
COUNT(*)
----------
14
SQL> commit;
Commit complete.
since we've committed, we'll see the session based rows but not the transaction based rows:
SQL>
SQL> select count(*) from temp_table_session
2 /
COUNT(*)
----------
14
SQL> select count(*) from temp_table_transaction
2 /
COUNT(*)
----------
0
SQL>
SQL> connect tkyte/tkyte
Connected.
SQL>
since we've started a new session, we'll see no rows now:
SQL>
SQL> select count(*) from temp_table_session
2 /
COUNT(*)
----------
0
SQL> select count(*) from temp_table_transaction
2 /
COUNT(*)
----------
0
SQL>
Instead of executing "select x, y, z into #temp from some_table" you would:
o once per database create "TEMP" as a global temporary table.
o then in your procedures you would simply "insert into temp (x,y,z) select x,y,y from some_table"
If you really need the temp table to be created in the procedure itself, Oracle8i release 8.1 makes this much easier to do as well. Consider the following example which uses plsql to create, insert into, fetch from and drop a temporary table -- whose name is not known until run time. Its almost as easy as static sql is:
SQL> declare
2 type mycur is ref cursor;
3
4 l_tname varchar2(30) default 'temp_table_' || userenv('sessionid');
5 l_cursor mycur;
6 l_ename scott.emp.ename%type;
7 begin
8 execute immediate 'create global temporary table ' ||
9 l_tname || ' on commit delete rows
10 as
11 select * from scott.emp where 1=0 ';
12
13 execute immediate 'insert into ' || l_tname ||
14 ' select * from scott.emp';
15
16 open l_cursor for
17 'select ename from ' || l_tname || ' order by ename';
18
19 loop
20 fetch l_cursor into l_ename;
21 exit when l_cursor%notfound;
22 dbms_output.put_line( l_ename );
23 end loop;
24
25 close l_cursor;
26 execute immediate 'drop table ' || l_tname;
27 end;
28 /
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD
PL/SQL procedure successfully completed.
Bear in mind that both CREATE and DROP are DDL and will commit any outstanding transactions.
follow up to comment:thats exactly what I mean. DDL is performed as:
commit;
ddl;
if success commit
else rollback;
see </code>
http://asktom.oracle.com/~tkyte/autonomous/index.html <code>
for a paper on autonomous transactions which may be useful in such a case.