Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Temporary Tables
A copy of this was sent to Norris <johnnie_at_cooper.com.hk>
(if that email address didn't require changing)
On 6 Jun 1999 03:58:31 GMT, you wrote:
>May I know the differences between Oracle8i 8.1.5 Temporary tables and
>SQLServer's Temporary tables?
they are similar to temp tables in those 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_transaction2 on commit delete rows
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> 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;
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Sun Jun 06 1999 - 08:07:31 CDT