How to use oracle temporary table?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: How to use oracle temporary table?

  1. #1
    Join Date
    Sep 2001
    Posts
    99

    How to use oracle temporary table?

    I want to use temporary table,but when i search in oracle websites,i only find a example about using globle temporary table. I want to a normal tempory table not a globle temporary table.
    And when i create a globle temporary table in a package function. A synex error occurs. Why? Could create statement not use in the package function? How to do,please tell me,thanks.thanks very much.
    ocean

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    You can glean quite a lot from this article... check this out ...
    http://dbasupport.com/oracle/ora8/page2.shtml

    HTH.

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

    Re: How to use oracle temporary table?

    Oracle doesn't have "normal" temporary tables.
    Global temporary tables should be created as other tables and persist in schema.
    Creating and dropping GTT inside a package isn't very good practice.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  4. #4
    Join Date
    Sep 2001
    Posts
    99

    oh,no,I want...

    the global temporary table is visible to all the sessions. I want to create temporary table in the package function and auto dropped when the session end.I want the temporary table created in a session and not visible to a another session.
    In sql server, I can create local temporay table with # name and create global temporary table with ## name.
    How to do in the oracle?
    ocean

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: oh,no,I want...

    Originally posted by oceanju
    I want the temporary table created in a session and not visible to a another session.
    The data is only visible to the session that created it - does it matter that other sessions know that the global temp table exists?

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828

    Re: oh,no,I want...

    Originally posted by oceanju
    the global temporary table is visible to all the sessions. I want to create temporary table in the package function and auto dropped when the session end.I want the temporary table created in a session and not visible to a another session.
    In sql server, I can create local temporay table with # name and create global temporary table with ## name.
    How to do in the oracle?
    Your #temp tables in sqlserver are equivalent to global temporary tables in oracle..The only difference being that you create the global temporary table once and for all.The data remains for either the duration of the session or for the duration of the time the user stays logged in.In the former case you create the table with on commit delete rows option and in the later case you use on commit preserve rows options..

    so to sum up

    sqlserver temp#=global temporary tables in oracle

    create them once and not every time in a proceedure..

    If you are migrating from sqlserver to oracle instead of #temp tables
    being replaced by Global temporary tables consider using inline views..

    regards
    Hrishy

  7. #7
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    Re: Re: oh,no,I want...

    Originally posted by hrishy
    The data remains for either the duration of the session or for the duration of the time the user stays logged in.
    The data remains for either the duration of the transaction or for the duration of the time the user stays logged in.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  8. #8
    Join Date
    Aug 2008
    Posts
    2
    Hi folks,

    It seems to me that the original question has not been answered, and 5 years later I now have the same question :( Any help appreciated.

    I have a PL/SQL proc running in 10g.
    In this procedure I execute a sequence of queries within a series of For Loops. The reult is the correct answer but terrible performance, and so it's been suggested that I try using GTTs (which I've never used before).

    Now while I have found many pages describing the syntax of creating GTTs, I can nevertheless find nothing that explains how I might use them in my situation. From within the PL/SQL proc I would like to create a GTT using the 'AS subquery' syntax, thus providing a GTT with simplified data for me to access later within the same proc and called subprocs.

    So at the start of my PL/SQL proc I tried to create the desired GTT, but I get a compile error complaining that it didn't expect the 'CREATE' keyword at that point.

    a) So as in the original post, why can't I create a GTT from within a PL/SQL proc?
    b) Ales said it's bad practice to try this. Why? At the moment it doesn't even seem possible, nevermind bad practice.
    c) If this is not possile, then I don't understand how I am supposed to use temp tables to assist my performance issue? They are supposed to be used to store intermediate data. Isn't that what I am trying to do?

    Many thanks for your help
    Assaf

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Take a look at pl/sql collections.
    this space intentionally left blank

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Well... actually the question got ansered five years ago.

    Going to your follow up questions...

    a) This is not best practice, that's why. You are supposed to create the GTT once and let it stay there.

    b) Do you know what "best practices" are?

    c) No, you do not understand. Yes, they are used to store "high volatility" data.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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