DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: TEMPORARY TABLE !!!

  1. #1
    Join Date
    Mar 2001
    Posts
    9
    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

  2. #2
    Join Date
    Dec 2000
    Posts
    10
    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.

  3. #3
    Join Date
    Mar 2001
    Posts
    9
    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

  4. #4
    Join Date
    Mar 2001
    Posts
    17
    Try with following

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

    Kancha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width