This discussion is archived
4 Replies Latest reply: Apr 8, 2012 11:01 AM by Tubby RSS

Dynamic Temp Table in Stored Procedure

929168 Newbie
Currently Being Moderated
Dear specialist,

I am trying to convert a MSSQL Database to Oracle 11g. But I am a little bit stuck on Dynamic temp tables in stored Procedures.

The SP is normalizing Data getting out of dynamic SQL Statements. So in TSQL i created a Temp table with the count of columns the SQL Statement had, than I run the SQL Statement into the temp table, and then I was goint to all collumns with the distinct command getting them normalized. Finally I was writing the Key table.

So if I want to do this process with only on SP, being Dynamic, rather than defining for each normalization process its own SP, is this possiple in Oracle?

For example. "INSERT INTO Temp (PartNo, Descr, Type, Price ) SELECT PartNo, Descr, Price FROM Parts"

PartNo| Descr| Type| Price
AB00| AKER GD245| Monitor| 200,00
AB01| BenL QQ22| Monitor| 120,00
AQ05| HB 5500DN| Printer| 550,00

SELECT @R = Max(PartKey) From N_Parts
INSERT INTO N_Parts (PartKey, PartNo) SELECT RowNum + @R, PartNo FROM (SELECT DISTINCT PartNo FROM Temp WHERE PartNo NOT IN (SELECT PartNo From N_Parts));
SELECT @R = Max(PartDKey) From N_PartsDescr
INSERT INTO N_PartsDescr (PartDKey, Descr) SELECT RowNum + @R, Descr FROM (SELECT DISTINCT Descr FROM Temp WHERE Desrc NOT IN (SELECT Desrc From N_Parts));
...

Insert Into Part_Data (PartKey,PartDKey,PartTKey,PartPKey)
SELECT T0.PartKey, T1.PartDKey, T2.PartTKey, T3.PartPKey FROM Temp T
INNER JOIN N_Parts T0 ON T0.PartNo = T.PartNo
INNER JOIN ....

So what is the best way, do generate this dynamic Temp Table?

Edited by: 926165 on 08.04.2012 08:26
  • 1. Re: Dynamic Temp Table in Stored Procedure
    rp0428 Guru
    Currently Being Moderated
    >
    So what is the best way, do generate this dynamic Temp Table?
    >
    You probably don't need a temp table in Oracle

    Your code is only using the temp table as intermediate storage for data that is then just queried later multiple times.
    In sql server temp tables are used a lot to avoid locking issues that cause performance problems when other users are querying some of the same data.

    Oracle doesn't have that locking problem. Readers do not block writers or other readers.
    So you can just repeat your query (the one that loads the temp table) when you need it.

    See this link from the AskTom site that talks about global temporary tables in Oracle
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15826034070548

    There are other links on this forum that talk about the same issue when migrating from sql server to Oracle.
    Temp_Tables_In_Oracle
  • 2. Re: Dynamic Temp Table in Stored Procedure
    929168 Newbie
    Currently Being Moderated
    Hi rp0428,

    thank you for the answer,

    what I did not mention was, that I do not use the temporary table because of tablelocks, I am using them because of performance Issues.
    The Tables I am doing the selects of, are quite large, so somehow it happens, that because of the "distinct" and the "not in" the select is taking a very long time, compared to writing the data in Temp Table and going from there.

    Next thing, I am using same technique is while serializing data from different Tables.

    So while Monitor has Parameters like, "screen size", "Backlight", printers come with "Color", "page per minute". Since in Shopping frontend they are shown Serialized, and I do not want to change all tables just to insert a new parameter, I prefer them safed serialized. But I usually get the data in tableformat, which I access through linked tables.
  • 3. Re: Dynamic Temp Table in Stored Procedure
    rp0428 Guru
    Currently Being Moderated
    >
    I do not use the temporary table because of tablelocks, I am using them because of performance Issues.
    >
    Global Temporary Tables in Oracle are different than temp tables in Sql Server.
    In Oracle the same table can be used by multiple users (sessions) without data conflicts; that is, to each user it appears as if it is their table and all the data in it belongs to them.

    The table is a permanent table; you create it one time. But the data in it will be deleted automatically. When the data is deleted depends on the way the table is defined.
    If you define it as
    ON COMMIT DELETE ROWS
    this is the default and when the session issues a COMMIT the table is truncated. Remember that only THIS sessions data is removed. Data for other sessions is unaffected.

    If you define the table as
    ON COMMIT PRESERVE ROWS
    Then the data is not deleted automatically until the session ends. Of course the user can delete or truncate it manually at any time.

    See Creating a Temporary Table in the DBA guide.
    http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#i1006400

    So unless that table has to dynamically change structure for each run of the stored procedure you can just define the global temp table once and then use it for multple sessions. You can also add any appropriate indexes on it and can add or drop columns as needed.

    If the table needs to have a different dynamic structure based on the dynamic numbers or types of columns in the result set that you create dynamically then you will need to create the table dynamically also. In this case there is no need to use a temp table since a regular table will do.

    NOTE - if you create the table dynamically then you MUST use dynamic sql to access it since the table will not exist at compile time and any reference to it will cause a compile error.
  • 4. Re: Dynamic Temp Table in Stored Procedure
    Tubby Guru
    Currently Being Moderated
    926165 wrote:
    I am trying to convert a MSSQL Database to Oracle 11g. But I am a little bit stuck on Dynamic temp tables in stored Procedures.
    Just my 2 cents here.

    Converting from Database Vendor A to Database Vendor B is never really a great idea. The products are fundamentally different in many respects. Analysing the application that needs to be ported from Database A to Database B and then implementing it in a specific manner to take advantage of the features of your new environment, accounting for any differences between Vendor implementations is going to be your best bet.

    I say this because, to me at least, a conversion means you are trying to take what you have and slam it in to what you need. The much better approach is to analyse, design (to account for any discrepancies in behaviour between Vendor implementations, such as read consistency mechanisms) and proceed from there. That way you have a solid foundation on which to begin, instead of a skyscraper built out of toothpicks.

    Cheers,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points