SQL & PL/SQL

Hi,

old story. Migrating SQL Server to Oracle. Now, we have about 400sps to convert and half of them are using temporary tables for some complex calculations.

My question is:

Select 3-4 different recordsets within one stored procedure, store them in temp tables (in SQL Server), join this recordsets with few other tables and return one recordset as a result. In TSQL you declare temp table, do some processing ( you cen join temp tavles with permanent tables) and when you finish drop the tables. Couldn't find equivalent in PL/SQL. Using global temporary tables does not sound very good (I would need 50+ temporary tables..). Using PL/SQL tables is not quite clearly explained in documentation...

Now I could use ordinary SQL like:

select T1.field1, T1.field2
from Table1 T1, (select filed1, field2 from Table2) T2, (select field1, field2 from Table3) T3
where T1.field1 = T2.field1
and T1.field1 = T3.field1.....

but sometimes I need to update second or third recordset. Dead end.

Please help,


SasaN