How to return more than one select queries in same procedure

Question

I would like to ask how can i print output in procedure more than one statement. Assume that you want to show dba_objects and segments row count. But i can not use dbms_sql.return_result my version is 11g.

Something like,

create or replace procedure get_rows_count
(
  cursor1 out SYS_REFCURSOR,
  cursor2 out SYS_REFCURSOR
)
as
begin
    open cursor1 for select count(*) from dba_objects;
    open cursor2 for select count(*) from dba_segments;
end get_rows_count;
/

Answer 1

Assume that you want to show dba_objects and segments row count

I assumed it. Conclusion: that's not the way to do it. If you want to get row count from e.g. dba_objects, then you should just

select count(*) from dba_objects;

in any variation you want (pure SQL, function that returns that number, procedure with an OUT parameter (worse option), ...). But, creating a procedure which uses ref cursor for that purpose is ... well, wrong.

---

If I got you wrong, then: procedure you wrote is OK. You can call it from another PL/SQL procedure (named or anonymous), fetch result into a variable and do something with it (e.g. display it).

Your procedure (selects from Scott's tables; I don't have access to DBA_ views):

SQL> CREATE OR REPLACE PROCEDURE get_rows_count (cursor1  OUT SYS_REFCURSOR,
  2                                              cursor2  OUT SYS_REFCURSOR)
  3  AS
  4  BEGIN
  5     OPEN cursor1 FOR SELECT * FROM emp;
  6
  7     OPEN cursor2 FOR SELECT * FROM dept;
  8  END get_rows_count;
  9  /

Procedure created.

How to call it? See line #8:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     rc1  SYS_REFCURSOR;
  3     rc2  SYS_REFCURSOR;
  4     --
  5     rw1  emp%ROWTYPE;
  6     rw2  dept%ROWTYPE;
  7  BEGIN
  8     get_rows_count (rc1, rc2);
  9
 10     DBMS_OUTPUT.put_line ('Employees -----------');
 11
 12     LOOP
 13        FETCH rc1 INTO rw1;
 14
 15        EXIT WHEN rc1%NOTFOUND;
 16
 17        DBMS_OUTPUT.put_line (rw1.ename);
 18     END LOOP;
 19
 20     --
 21     DBMS_OUTPUT.put_line ('Departments ---------');
 22
 23     LOOP
 24        FETCH rc2 INTO rw2;
 25
 26        EXIT WHEN rc2%NOTFOUND;
 27
 28        DBMS_OUTPUT.put_line (rw2.dname);
 29     END LOOP;
 30
 31     DBMS_OUTPUT.put_line ('First ref cursor: ' || rc1%ROWCOUNT);
 32     DBMS_OUTPUT.put_line ('Second ref cursor: ' || rc2%ROWCOUNT);
 33  END;
 34  /

Result:

Employees -----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
Departments ---------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
First ref cursor: 14
Second ref cursor: 4

PL/SQL procedure successfully completed.

SQL>