I’m trying to create a procedure in Oracle that would return the result of the query and at the same time count the number of returned rows. Is it possible to do that in one procedure?
This one returns the result of the query (this is an example query, our production query is more complicated):

CREATE OR REPLACE PROCEDURE GETCUR(PARAM1 VARCHAR2)
AS
cur SYS_REFCURSOR;
cSql NUMBER;
cnt INTEGER;

BEGIN
   OPEN CUR FOR
   SELECT T1.F3, T2.F3 FROM T1 JOIN T2 ON T1.F1 = T2.F2 WHERE T2.F9 = PARAM1;
   
   DBMS_SQL.RETURN_RESULT(CUR);
END;

And this one can count the number of returned rows. Here I print it using PUT_LINE but want to be able to assign it to some variable and insert that into other table – something like a logging mechanism.

CREATE OR REPLACE PROCEDURE GETCUR(PARAM1 VARCHAR2)
AS
cur SYS_REFCURSOR;
cSql NUMBER;
cnt INTEGER;
       
BEGIN
  OPEN CUR FOR
    SELECT T1.F3, T2.F3 FROM T1 JOIN T2 ON T1.F1 = T2.F2 WHERE T2.F9 = PARAM1;
           
--DBMS_SQL.RETURN_RESULT(CUR);

cSql := DBMS_SQL.TO_CURSOR_NUMBER(CUR);
cnt := 0;
LOOP
  EXIT WHEN DBMS_SQL.FETCH_ROWS(cSql) = 0;
  cnt := cnt + 1;
END LOOP;
    
DBMS_SQL.CLOSE_CURSOR(cSql);
DBMS_OUTPUT.PUT_LINE(cnt||' rows returned');
    
END;

But I can’t marry these two solutions into one procedure. Is it possible?