for getting result sets from stored procedures.


In this case, I would recommend you totally skip the temporary tables and their unneccesary IO and overhead and go right for the answer.

We support temporary tables, but in a different fashion, see:

for a comparision.  DO NOT dynamically create them, DO NOT dynamically create them, please -- do NOT dynamically create them.


<b>followup to comment one below</b>

see

for examples of ref cursors and their usage.

Anytime you can rewrite in a single query what you are currently doing procedurally or in many steps -- you will be the better for it.

In the example above, they were catering to a database engine that did not perform optimization very well with lots of tables ( very common in that database, that is why temp tables are so overused ).  In Oracle -- even with "real temp tables" in 8i -- this would be the wrong way to approach this problem.

In their case, using a ref cursor is easy, straight forward and cuts out the temp table all together.  It is important to note however that even with the temp table -- they would still need to use a ref cursor (as that is the way to return result sets from stored procedures)

Whether or not you can make use of them will have to be determined by you ultimately.



<b>follow to comment two</b>

Karl -- don't know exactly what you are asking but... here is an example.  Suppose in SQLserver you had coded something like:

begin
   select a,b,c into #t1
     from t1, t2, t3
     where t1.x = t2.y and t2.y = t3.z and t1.c1 = 2;


    select a,d,e,f into #t2
      from t4, t5, t6
     where t4.d = t5.e and t5.e = t6.f and t6.c2 = 55;

    select a.a, a.b, a.c, b.d, b.e, b.f
      from #t1 a, #t2 b
     where a.a = b.a;
end;

In Oracle we would just code:

begin
    open ref_cursor_variable for
    select a.a, a.b, a.c, b.d, b.e, b.f
      from (select a,b,c into #t1
              from t1, t2, t3
              where t1.x = t2.y and t2.y = t3.z and t1.c1 = 2) a, 
           (select a,d,e,f into #t2
              from t4, t5, t6
             where t4.d = t5.e and t5.e = t6.f and t6.c2 = 55) b
      where a.a = b.a;
end;

and that is it -- where you used temp tables, we'll use INLINE views.  Hope that clears it up, if not, feel free to followup.

<b>followup to comment three</b>

No, you are missing the point.

You don't open a REF CURSOR / temporary table from SQLServer -- you use an INLINE view / temp table.  

For example:

scott@ORA8I.WORLD> variable result_set refcursor
scott@ORA8I.WORLD> 
scott@ORA8I.WORLD> begin
  2    open :result_set for
  3          select a.ename, b.dname
  4            from ( select * from emp ) a,
  5                     ( select * from dept ) b
  6           where a.deptno = b.deptno;
  7  end;
  8  /

PL/SQL procedure successfully completed.

scott@ORA8I.WORLD> 
scott@ORA8I.WORLD> print result_set

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
miller     ACCOUNTING

14 rows selected.

Here "select * from emp" and "select * from dept" represent your "temp table queries" from sqlserver.

If a third table (or fourth or 20th, whatever) needs to be involved -- so be it, just put it in the from clause with the rest of them.


As you can see -- that result_set has >1 row!



<b>followup to comment 4</b>

Karl -- back to my original point here -- we are <b>not</b> sqlserver.

fears like: "I am afraid that the performance would be low because there are many tables opened simultaneously." would be valid there, they are not here in Oracle.

One suggestion -- just try it.  Take a look at my free.sql query: