Using an Oracle stored procedure with an implicit result set in another stored procedure

Question

I have an Oracle (21c) stored procedure that returns a cursor as an implicit result set using DBMS_SQL.RETURN_RESULT. I have another stored procedure that needs to call this first stored procedure and use that cursor without passing an OUT parameter for the cursor.

CREATE OR REPLACE NONEDITIONABLE PROCEDURE TESTPROC 
(
  V_USERACCOUNTID IN VARCHAR2 
) AS 
  V_CURSOR SYS_REFCURSOR;
BEGIN
  OPEN V_CURSOR FOR
  SELECT * FROM USERACCOUNT WHERE ID = V_USERACCOUNTID;
  
  DBMS_SQL.RETURN_RESULT(V_CURSOR);
END TESTPROC;


CREATE OR REPLACE NONEDITIONABLE PROCEDURE USETESTPROC 
(
  V_USERACCOUNTID IN VARCHAR2 
) AS 
  V_CURSOR SYS_REFCURSOR;
BEGIN
  TESTPROC(V_USERACCOUNTID); 
  -- how do you get the implicit result set from here into V_CURSOR?
  
  DBMS_SQL.RETURN_RESULT(V_CURSOR);
END TESTPROC;

Answer 1

With a Procedure as in your question:

CREATE OR REPLACE PROCEDURE TESTPROC 
 AS 
  V_CURSOR SYS_REFCURSOR;
BEGIN
  OPEN V_CURSOR FOR
  SELECT 1 "A_NUMBER", 'A' "A_LETTER" FROM Dual UNION ALL
  SELECT 2 "A_NUMBER", 'B' "A_LETTER" FROM Dual;
  
  DBMS_SQL.RETURN_RESULT(V_CURSOR);
END TESTPROC;

You can use the resultset with PL/SQL but you should do it using DBMS_SQL Package ... (comments in code)

SET SERVEROUTPUT ON
DECLARE
  p_sql_cursor    PLS_INTEGER;
  p_ref_cursor    SYS_REFCURSOR;
  p_return        PLS_INTEGER;
  p_number        NUMBER(6);
  p_letter        VARCHAR2(12);
BEGIN
    -- Initialize cursor with DBMS_SQL.open_cursor()
    p_sql_cursor := DBMS_SQL.open_cursor(treat_as_client_for_results => TRUE);
    -- Parse TESTPROC Using DBMS_SQL.parse()
    DBMS_SQL.parse(c             => p_sql_cursor,
                   statement     => 'BEGIN TESTPROC; END;',
                   language_flag => DBMS_SQL.native);
    -- Execute 
    p_return := DBMS_SQL.execute(p_sql_cursor);
    -- Get the next resultset.
    BEGIN
      DBMS_SQL.get_next_result(p_sql_cursor, p_ref_cursor);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        Null;
    END;
      --  Process it
        LOOP
          FETCH p_ref_cursor
          INTO  p_number, p_letter;

          EXIT WHEN p_ref_cursor%NOTFOUND;

          DBMS_OUTPUT.put_line('p_number=' || p_number || '  ' ||
                               'p_letter=' || p_letter);
        END LOOP;
        CLOSE p_ref_cursor;
END;
/

/*    R e s u l t :
p_number=1  p_letter=A
p_number=2  p_letter=B

PL/SQL procedure successfully completed.    */

OR you can just EXEC the procedure (SQLDeveloper) to get the resultset :

EXEC TESTPROC;
/*    R e s u l t :
  A_NUMBER A_LETTER
---------- --------
         1 A
         2 B        */