SQL & PL/SQL

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