How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle?

Question

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?

• gbn: I'll say that code like that is indicative of procedural not set based thinking. And these are local temp tables with a single #. I am management and I'd break legs if I saw that going into production :-)
• bernd_k: I agree completely
• ConcernedOfTunbridgeWells: @gbn - Idiomatic PL/SQL tends to be a bit more procedural than idiomatic T-SQL. Temp tables make it possible to do nearly everything in set ops in T-SQL. PL/SQL has parallel cursor ops and a lot more functionality for optimising procedural code.

Answer 1

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:

SQL> create or replace type t1_type as object (x int, y int, z int)
 2 /
Type created.
SQL> create or replace type t1 as table of t1_type
 2 /
Type created.

Now set up some sample data:

SQL> create table xy (x int, y int)
 2 /
Table created.
SQL> insert into xy values (1, 2)
 2 /
1 row created.
SQL> insert into xy values (3, 4)
 2 /
1 row created.
SQL> commit
 2 /
Commit complete.

And create a function over this data returning our "temporary" type:

SQL> create or replace function fn_t1 return t1 as
 2 v_t1 t1 := t1(); -- empty temporary table (really an array)
 3 v_ix number default 0; -- array index
 4 begin
 5 for r in (select * from xy) loop
 6 v_ix := v_ix + 1;
 7 v_t1.extend;
 8 v_t1(v_ix) := t1_type(r.x, r.y, (r.x + r.y));
 9 end loop;
 10 return v_t1;
 11 end;
 12 /
Function created.

And finally:

SQL> select * from the (select cast (fn_t1 as t1) from dual)
 2 /
 X Y Z
---------- ---------- ----------
 1 2 3
 3 4 7

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).

Answer 2

If the case option isn't flexible enough you could bulk collect the data in your procedure and manipulate the array(s).

--Setup
CREATE TABLE table_a (c1 Number(2));
CREATE TABLE table_b (c1 Number(2));
INSERT INTO table_a (SELECT rownum FROM dual CONNECT BY rownum<=4);
INSERT INTO table_b (SELECT rownum+5 FROM dual CONNECT BY rownum<=4);
--Example
DECLARE
 Type tNumberArray Is Table Of Number;
 v1 tNumberArray;
BEGIN
 SELECT c1 BULK COLLECT INTO v1 FROM (
 SELECT c1 FROM table_a
 UNION ALL
 SELECT c1 FROM table_b
 );
 For x IN v1.First..v1.Last Loop
 /* Complex manipulation goes here. */
 If (v1(x) <= 3) Then
 v1(x) := v1(x)*10;
 End If;
 DBMS_OUTPUT.PUT_LINE(v1(x));
 End Loop;
END;
/

• Gaius: +1, but this doesn't return a result set (if a SELECT is the last thing in a T-SQL stored proc, that's what it returns)
• Leigh Riffel: If this code block were turned into a procedure you could return the array or open the arrays as a cursor and pass the cursor back, or even make it a function and pipe back the rows. Each of these would be similar, but which you should use would depend on the situation.