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;
/

score 2 · Accepted Answer · answered Feb 09 '23 at 08:01

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>

you are right i forgot to type count(*) thanks i will try your block to. — microracle, Feb 09 '23 at 08:13
`COUNT(*)` should perform WAY BETTER than any kind of loop (and, if you work with cursors, you have to loop; there's no way to find out how many rows it returns until you reach the end). If cursor returns small number of rows, you won't notice any difference, but DBA_OBJECTS can be large so it'll take time to find out that simple information - number of rows within. SELECT COUNT(*) is the fastest way to do it. — Littlefoot, Feb 09 '23 at 08:30
Thank you so much for detail information. I will keep in mind. — microracle, Feb 09 '23 at 08:45

Barbaros Özhan · Answer 2 · 2023-02-09T13:35:32.907

1

You can use famous DBMS_OUTPUT.PUT_LINE() along with %ROWCOUNT suffix for your case such as

SET serveroutput ON
CREATE OR REPLACE PROCEDURE get_rows_count(
                                            cursor1 OUT SYS_REFCURSOR,
                                            cursor2 OUT SYS_REFCURSOR,
                                            count1  OUT INT,
                                            count2  OUT INT
                                          ) AS
  cur_rec_obj user_objects%ROWTYPE;
  cur_rec_seg user_segments%ROWTYPE;      
BEGIN
    OPEN cursor1 FOR SELECT * FROM user_objects;
    LOOP
      FETCH cursor1 INTO cur_rec_obj;  
      EXIT WHEN cursor1%NOTFOUND;
    END LOOP;

    OPEN cursor2 FOR SELECT * FROM user_segments;
    LOOP
      FETCH cursor2 INTO cur_rec_seg;  
      EXIT WHEN cursor2%NOTFOUND;
    END LOOP;

    count1 := cursor1%ROWCOUNT;
    count2 := cursor2%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE(count1);
    DBMS_OUTPUT.PUT_LINE(count2);    
      
END;
/

and you can call as follows from the SQL Window of PL/SQL Developer :

DECLARE
 v_cursor1   SYS_REFCURSOR;
 v_cursor2   SYS_REFCURSOR;
 v_count1    INT;
 v_count2    INT;
BEGIN
  get_rows_count(v_cursor1, v_cursor2, v_count1, v_count2 );
END;
/

edited Feb 09 '23 at 13:35

answered Feb 09 '23 at 07:53

Barbaros Özhan

59,113
10
31
55

When i call proc i am getting below error did i miss something? ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_ROWS_COUNT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored – microracle Feb 09 '23 at 08:22
How do you call the procedure @microracle . Btw, I've just added two new out parameters as well to represent those counts. – Barbaros Özhan Feb 09 '23 at 09:57
I have tried many ways to call. Can you help to me about how should i call? – microracle Feb 09 '23 at 11:53
1

by the way i am very sorry for your lost on earthquake :(( – microracle Feb 09 '23 at 12:57
1

Thank you very much @microracle , I appreciated. Unfortunately it's really devastating ... Btw, edited the answer. Take care. – Barbaros Özhan Feb 09 '23 at 13:37

How to return more than one select queries in same procedure

2 Answers2