Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    1

    Unanswered: Oracle Temporary Tables

    Can anyone give an actual example for using temporary tables in Oracle, without giving the same explanation of what they are, and how to create them. I've got that! What I'm looking for is the SQL Server equivalent of the following:

    SELECT MAX(UpdateDate) AS UpdateDate
    , KeyField
    INTO #TmpTable
    FROM RealTable1
    WHERE {Condition is met}

    SELECT rt2.FieldOne
    , tmp.KeyField
    , rt3.FieldFive
    FROM RealTable2 rt2
    INNER JOIN #TmpTable tmp
    INNER JOIN RealTable3 rt3
    ON tmp.KeyField = rt3.KeyField AND tmp.UpdateDate = rt3.UpdateDate
    ON rt2.KeyField = tmp.KeyField

    drop table #TmpTable


    Is there a reason why I can't find an Oracle equivalent example anywhere? Does Oracle not allow this? Is there anyone who can help?

    Thank you!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there a reason why I can't find an Oracle equivalent example anywhere?
    As a general rule, Oracle does not need to use "temporary table".

    This is like asking a Ferrari to perform like VW Beetle.

    If you insist, research Global Temporary Tables (GTT) on Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2009
    Posts
    5
    I had the same question when i moved from a SQL Server shop to an Oracle shop about 5 months ago. Generally Oracle is not designed to use #temp tables, in fact if you talk to a developer that has never used SQL Server they really have no clue what you are talking about. There idea of a temp table is to actually create a table and call it temp and not an virtual temp table that only lives within the SP.

    The one thing you have to get over when moving to Oracle from SQL Server is the use of cursors, I know that no respectable SQL Server developer would ever use a cursor but Oracle is designed to utilize cursors much in the same fashion that SQL server is designed to use #tmp tables.

    So the easy answer to your question is that equivalent to #tmp tables in Oracles are Cursors.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    temp tables in SQL Server are more akin to inline views in Oracle, IMO

    Code:
    SELECT rt2.FieldOne
    , tmp.KeyField
    , rt3.FieldFive
    FROM RealTable2 rt2
         INNER JOIN (SELECT MAX(UpdateDate) AS UpdateDate , KeyField
                     FROM RealTable1
                     WHERE {Condition is met}) tmp
         INNER JOIN RealTable3 rt3
           ON tmp.KeyField = rt3.KeyField AND tmp.UpdateDate = rt3.UpdateDate
           ON rt2.KeyField = tmp.KeyField
    Although I think that SQL Server can perform INLINE VIEWS now.

    I wouldn't consider cursors = temp tables.

    --=Chuck

  5. #5
    Join Date
    Jul 2009
    Posts
    5
    I took a look at his query and agree with you the way he is showing the use of temp table would be like an inline view.

    In case he is looking a an exact match for all temp table uses in Oracle there is none as there are multiple uses for temp tables in SQL Server which are best done in other ways with Oracle.

    One of the most common uses is to step through and manipulate other tables similar to how you would use a cursors.

    The other common use is to create return recordsets from a SP. I beleive in oracle this can be done by creating a recordset or table, I am new to Oracle as well and have not had to create a recordset for this purpose so I might be a little off on the best way to create a return recordset.

    The one thing I know is that it took awhile to stop looking for the equivalent from SQL Server and just learn the best way to work in Oracle.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Apart from the fact that SQL Server creates temporary tables implicitly, while in Oracle you have to declare them by using CREATE GLOBAL TEMPORARY TABLE, their behaviour is quite similar. Oracle temporary table definitions are persistent across sessions, however, each session has its own "version" of a temporary table, in other words, data inserted by one session are not visible by other sessions.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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