Session level temp tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Session level temp tables

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

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

  3. #3
    Join Date
    Mar 2001
    Posts
    9

    Hi 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


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

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


  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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