Re: Temporary Tables

Thomas,
1. Can you explain a little more about how temporary table are used in an application environment ?
2. what kind work actually take place by oracle ?

   (user can only see the data he created and when his session terminate the data is gone).
3. above what version of oracle this is available ? Thanks
Ted Chyn

Thanks Ted
In article <3762725a.17936581_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
> 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_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.
>
> 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
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 10 1999 - 15:55:36 CDT