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?
Best Answer
One way to do this would be Object Types, in this case the type would be analagous to your
#t1
. So it would need to be defined somewhere but it would not need to be global, it could be per-schema or per-procedure even. First, we can create a type:Now set up some sample data:
And create a function over this data returning our "temporary" type:
And finally:
As you can see this is pretty clunky (and uses collection pseudo-functions, which is an obscure feature at the best of times!), as I always say, porting from DB to DB is not merely about syntax and keywords in their SQL dialects, the real difficulty comes in different underlying assumptions (in the case of SQL Server, that cursors are expensive and their use avoided/worked around at all costs).