Re: CREATE GLOBAL TEMPORARY TABLE in Oracle 8i

Thanks, Thomas.

I've tried it out, and it works.

Best Wishes,

Kochin Chang

In article <372514e2.5499497_at_192.86.155.100>,   tkyte_at_us.oracle.com wrote:
> 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_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>
>
> 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.
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sat Apr 24 1999 - 16:27:21 CDT