C# developer encouraged by management to write SQL Server stored procedures often produce procedures like this
create table #t1 (...);
insert into #t1 Select ... from table_a where ...;
insert into #t1 Select ... from table_b where ...;
update #t1 Set ... = ... where ...
Select * from #t1;
The single statement are rather simple and this method makes them produce correct results.
Often my task is to migrate such procedures to Oracle.
Let's face the following facts.
- To different temporary tables in SQL Server are completely independent and can have any ad hoc structure.
- Oracle global common tables are global objects and all uses much share the same table structure. Modifying this structure is impossible, while it is used anywhere.
One of the things I learned from an Oracle dba, was to avoid the use of temporary tables whenever it is possible. Even the performance on SQL server benefits from such modifications.
Replace the individual inserts by unions
In the most simple case, the above can be transformed into something like
select case when ... then ... end, ... from table_a where ...
union
select case when ... then ... end, ... from table_b where ...
Order by ...;
Use of functions
Both scalar functions and table valued functions can help to transform your procedure into a single query of the above form.
Common Table expressions aka Subquery Factoring
Subquery Factoring is nearly the best Oracle has to offer to avoid temporary tables. Using it the migration of SQL Server to Oracle is again rather easy. This requires SQL Server 2005 and above.
These modifications improve the SQL Server version and in many cases make the migration straight forward. In other cases resort to global temporary tables makes it possible to do the migration in a bounded time, but it is less satisfying.
Are there further ways to avoid the use of global temporary tables in Oracle?