Oracle datasets from two different tables without joining

Question

I need to write a stored procedure that will provide the data from two different tables. Say table1 and table2. These two tables doesn't have any relationship.

Now in SQL Server i can simply create a stored procedures like:

create procedure abc
as
begin
select * from table1;
select * from table2:
end;

Now in oracle, I usually create a SYS_REFCURSOR and do something like:

Open SYS_REFCURSOR_VAR For Select * from table1;

but I don't know how to provide the two result sets from two different tables table1 and table2. I tried to create two different SYS_REFCURSOR one for each table. But when I executed the procedure I got the data from first table only. The second SYS_REFCURSOR doesn't seems to be working.

Anyone have any idea, how to accomplish this?

Answer 1

Try this

create or replace procedure tst
 (p_refcursor1 out sys_refcursor,p_refcursor2 out sys_refcursor)
 is 

begin

open p_refcursor1 for
 select * from dual;

open p_refcursor2 for
 select * from dual;



end;

Answer 2

So I assume the records you want to pull from each table are effectively the same. e.g. id, name, price. In that case just write your query like

SELECT t1.id AS id, t1.name AS name, t1.unit_price AS price FROM t1
UNION
SELECT t2.id AS id, t2.description AS name, t2.price AS price FROM t2

Not sure if its required, but always good form to have a union return data sets with the same "column" names. So I used the AS in teh example to demonstrate this

Answer 3

You need to give more details about your problem.. specifically 1) the code that you have tried so far 2) How are you accessing your ref cursor to see the results? (SQLPLUS or Java or VB.net)?

Based on your SQL Server code, I am assuming you are trying to get the UNION of the rows from the two tables. Here is the code in Oracle that lets you do that. As you can see, my client tool here is SQLPLUS and I am able to see the values from both the tables.

create table t1(
id number,
name varchar2(10)
);

create table t2(
id number,
name varchar2(10)
);

create or replace procedure get_t1_and_t2(
    o_cur out sys_refcursor) is
begin
  open o_cur for
     select id from t1
     union all
     select id from t2;
end;
/

Procedure created.

SQL> var rc refcursor;
SQL> exec get_t1_and_t2( :rc);

PL/SQL procedure successfully completed.

SQL> print rc;

        ID
----------
         1
         2

Answer 4

This script shows @Maxim Shevtsov answer working. Run it in SQL*Plus

SET serveroutput ON size 100000

DECLARE
    c1 SYS_REFCURSOR;
    c2 SYS_REFCURSOR;
    v1 VARCHAR2(10);
    v2 VARCHAR2(10);
    n2 NUMBER;

    PROCEDURE tst ( p_refcursor1 OUT SYS_REFCURSOR
                  , p_refcursor2 OUT SYS_REFCURSOR)
    IS
    BEGIN
        OPEN p_refcursor1 FOR
            SELECT 'val1' FROM DUAL;
        OPEN p_refcursor2 FOR
            SELECT 'val2', 42 FROM DUAL;
    END tst;

BEGIN
    tst( c1, c2 );
    LOOP
        FETCH c1 INTO v1;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.put_line( 'CURSOR1: ' || v1 );
    END LOOP;
    LOOP
        FETCH c2 INTO v2, n2;
        EXIT WHEN c2%NOTFOUND;
        DBMS_OUTPUT.put_line( 'CURSOR2: ' || v2 || ' ' || n2 );
    END LOOP;
END;
/