| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: CREATE GLOBAL TEMPORARY TABLE in Oracle 8i
A copy of this was sent to kochinc_at_yahoo.com
(if that email address didn't require changing)
On Thu, 22 Apr 1999 11:36:19 GMT, you wrote:
>Hi,
>
>Oracle 8i's document mentions a new temporary table construct:
> CREATE GLOBAL TEMPORARY TABLE
>
>Has anyone tried this out? Is it similar to the temp table in Sybase and MS
>SQL? Can anyone provide a simple example?
>
>Thanks in advance!
>
>Kochin Chang
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
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;
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |