Session level temp tables

Hi,

I want to create a session level temporary tables..

declare
a varchar2(200);
begin
a := 'create global temporary table sam on commit preserve rows as select * from emp';
execute immediate a;
end;

How to create and drop the the sam table within the loop.

Thanxx

sam

Hello Sam,

When you create a table with SQL: 'CREATE GLOBAL TEMPORARY TABLE ...' you create a table at session level. I don't understand why you want to drop your temporary table. Could you explain what you want to do?

Best Regards,
Steph.

Hi steph,


I have created a table with SQL: 'CREATE GLOBAL TEMPORARY TABLE ...' I want to drop that (temporary!!) table becos the table exists even after i close my session...I cant understand what's the problem..

With regards

Sam

Sam,

To drop the temporary table you need first to truncate the table sam and after to drop it.

Ex:
SQLWKS> declare
2> a varchar2(200);
3> begin
4> a := 'create global temporary table sam on commit preserve rows as select * from toto';
5> execute immediate a;
6> end;
7>
8>
9>
Statement processed.
SQLWKS> truncate table sam
2>
Statement processed.
SQLWKS> drop table sam
2>
Statement processed.

Hope this help,
Steph.

Hi steph,


I am going to use this temporary table is my procedure.After the execution of the procedure the temporary table has to be dropped automatically.I want that the temp table should not exist after the end of TRANSACTION..If the procedure is executed agian, then once again has to create the table...
I want the table to be transaction specific..

Sam

Can you explain why you wan't your temporary table to be dropped in the same session in which it has been created? Oracle concept of temporary tables simply does not cause any need to drop temporary tables at all.

The concept of Oracle's temporary tables is called *GLOBAL* temporary tables, as opposed to the concept of *LOCAL* temporary tables as it is used in MS SQL and some others (at least I was told so about MS SQL, I'm far from being an expert on MS SQL) . With *global* temporary tables, the definition of the table is kept in the dictionary on disposal to each and every user with sufficient privileges, while the contents of the table is available only per session/transaction level. This concept is very efficient, I realy don't see why you should drop such a temporary table at all.