(Based on a Stack Exchange post)

In SQL Server, a common pattern is to wrap some processing in a stored procedure that returns a standard result set, using a local temporary table, which is to say a table defined within the SP and existing only within its scope. This pattern doesn’t carry over well to Oracle, as temporary tables there are global, permanent structures; only the contents are temporary (they are visible within a session, or until a transaction completes). So assuming that you have a client application that expects to execute a query, retrieve a result set and use that somehow, how do you go about porting its back-end between SQL Server and Oracle? This is my technique based on Object Types in Oracle.

First consider this trivial stored procedure in T-SQL, no parameters, it just reads a table, does some processing, and passes on the results:

create table xy (x int, y int)
go

insert into xy values (1,2)
insert into xy values (3,4)
go

create procedure sp_xy as
create table #t1 (x int, y int, z int)
insert into #t1 (x, y) (select * from xy)
update #t1 set z = x + y
select * from #t1

exec sp_xy

The nearest equivalent to this in Oracle is:

create or replace type t1_type as object (x int, y int, z int);
create or replace type t1 as table of t1_type;

create or replace function fn_t1 return t1 as
    v_t1 t1 := t1();       -- empty temporary table (really an array)
    v_ix number default 0; -- array index
    begin
        for r in (select * from xy) loop
            v_ix := v_ix + 1;
            v_t1.extend;
            v_t1(v_ix) := t1_type(r.x, r.y, (r.x + r.y));
        end loop;
        return v_t1;
    end;
/

select * from the (select cast (fn_t1 as t1) from dual);

Which from the perspective of a client application gives exactly the same kind of result set

         X          Y          Z
---------- ---------- ----------
         1          2          3
         3          4          7

Obviously this technique is a little clunky, but that is always the issue when porting applications in the real world: it’s not just about differences in syntax or in keywords, rather it is about a mismatch between underlying concepts and assumptions (and it’s about whether you are porting or rewriting). For example in SQL Server # (and ##) tables are common practice and cursors are expensive and to be avoided unless strictly necessary (an artefact of the way it does locking and concurrency). In Oracle every SELECT is a cursor anyway, all declaring it does is give you a handle (r) by which to manipulate it. In Oracle it’s not common at all to coerce a tabular structure that isn’t actually a table, and relies on collection pseudo-functions which are slighly obscure. Going the other way, I’d use a SQL Server table variable to achieve the same effect.

One question I usually ask DBA candidates is about this, having had to deal with it indicates a certain level of real-world experience and this is far from the only technique. Why wouldn’t you just use an Oracle temporary table, exec the SP to populate it then SELECT as normal? I’ll save that for the interview…