CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
microracle
microracle

Reputation: 69

How to return more than one select queries in same procedure

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

Upvotes: 1

Views: 177

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 2

Related Questions