Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    India
    Posts
    55

    Unhappy Unanswered: Temporary Tables

    Hi,


    Could any experts give me a sample syntax to create a temporary table.
    Please dont refer me any other sites becs i ve already landed in many sites which asks for 90$ after the fifth page and no results.

    Thanx in Advance and Regards
    Sree

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Temporary Tables

    Originally posted by sreeraj
    Hi,


    Could any experts give me a sample syntax to create a temporary table.
    Please dont refer me any other sites becs i ve already landed in many sites which asks for 90$ after the fifth page and no results.

    Thanx in Advance and Regards
    for example:

    create global temporary table tmp_mytable (
    name varchar2(100)
    ) on commit delete rows;

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Temporary Tables

    If you need the data to remain after you commit use the preserve rows keyword.

    Code:
    create global temporary table tmp_mytable (
        name varchar2(100)) 
    on commit preserve rows;
    This will allow the data to remain in the table after a commit is encountered. Remember that the data is only persistent in the current session.

    hih

  4. #4
    Join Date
    Oct 2003
    Location
    India
    Posts
    55
    Thank you
    Sree

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074

    Another question

    Is a global temporary table available to any other sessions? In Sybase, a temp table can created for either a single user session, or a slightly different type can be created which any session can see (really, it turns out to behave more like a permanent table).

    What do I need to look out for as we convert to Oracle?
    -cf

  6. #6
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Another question

    You can do both. If you want a temp table for a single user, create the table under that user. Every time the user creates a session, the table will be avialable within their schema.

    If you want every user to see the same table you can create 1 temp table and grant it to all the users that need it. Again the data is only visible to the current session. So even though the same table is being used by different users, the data in the table is only visible to the correct session.

    HIH

Posting Permissions

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