TEMPORARY TABLE !!!

[MSDWSAM123]

Hi,

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

When executing this part,only the table structure is created..But the values from the emp table are not inserted.
We are using 8.1.6.

write back: sampath20@rediffmail.com

Sam

[SCORBET]

Hello Sam,

When you use SQL "create global temporary table sam as select * from temp" I think your do an implicit commit.
As you don't specify any statement for action "on commit" all your rows from temp are deleted.

Maybe you could try to change your statement with "create global temporary table sam on commit preserve rows as select * from temp"?


Regards,
Steph.

[MSDWSAM123]

Hi steph,
It is working fine with ON COMMIT PRESERVE ROWS.But i want it that at session level..The table should be created and droped with in the block automatically.How to create session level temp tables..I tried with ON COMMIT DELETE ROWS..

SAM

[Kancha]

Try with following

CREATE global temporary TABLE sam as select * from emp
on commit preserve rows

Kancha