Re: multiple result sets from a procedure??

In article <d0bb6654.0107090641.2550b078_at_posting.google.com>, cmercer_at_vibrant-1.com says...
>
>I have seen and tried example code from these news groups that allow a
>PL/SQL function to return a ref cursor to JAVA, and a PL/SQL function
>return a result set to JAVA. Now, here's another question.... Can a
>procedure or function return multiple result sets or ref cursors? I
>now work at a company that loves Sybase and I am trying to convince
>them that Oracle is better. They use Sybase stored procedures and
>claim (which I believe) that both procedures and functions can return
>multiple result sets to the calling code. Any way to simulate this in
>Oracle?
>
>Carl
>cmercer_at_vibrant-1.com

No need to "simulate" this, just code it:

ops$tkyte_at_ORA817.US.ORACLE.COM> create or replace package types   2 as
  3 type rc is ref cursor;
  4 end;
  5 /

Package created.

ops$tkyte_at_ORA817.US.ORACLE.COM> 
ops$tkyte_at_ORA817.US.ORACLE.COM> 
ops$tkyte_at_ORA817.US.ORACLE.COM> create or replace
  2  procedure p( c1 in out types.rc,
  3               c2 in out types.rc,
  4               c3 in out types.rc )

  7      open c1 for select * from emp where rownum = 1;
  8      open c2 for select * from dept where rownum = 1;
  9      open c3 for select * from dual;

Procedure created.

ops$tkyte_at_ORA817.US.ORACLE.COM> 
ops$tkyte_at_ORA817.US.ORACLE.COM> 
ops$tkyte_at_ORA817.US.ORACLE.COM> variable x refcursor
ops$tkyte_at_ORA817.US.ORACLE.COM> variable y refcursor
ops$tkyte_at_ORA817.US.ORACLE.COM> variable z refcursor
ops$tkyte_at_ORA817.US.ORACLE.COM> set autoprint on
ops$tkyte_at_ORA817.US.ORACLE.COM> 
ops$tkyte_at_ORA817.US.ORACLE.COM> exec p( :x, :y, :z )

PL/SQL procedure successfully completed.

D
-
X

    DEPTNO DNAME LOC
---------- -------------- -------------

        10 ACCOUNTING NEW YORK EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DROPC
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -----

7369 SMITH      CLERK           7902 17-DEC-80        800                    20

See
http://osi.oracle.com/~tkyte/ResultSets/index.html for examples using ref cursors in many different languages.

--
Thomas Kyte (tkyte_at_us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp