How to return more than one select queries in same procedure

February 9, 2023

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 * from dba_objects;
    open cursor2 for select * from dba_segments;
end get_rows_count;
/

>Solution :

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


    DBMS_OUTPUT.PUT_LINE(cursor1%ROWCOUNT);
    DBMS_OUTPUT.PUT_LINE(cursor2%ROWCOUNT);    
      
END;
/