PLS-00382: "expression is of wrong type" when opening REF CURSOR on stored procedure called in a loop

Question

I have an outer stored procedure which is calling an inner stored procedure in a loop. The inner stored procedure returns a REF CURSOR consisting of 2 parts, (1) V_SUCCESSFUL_COUNT (2) V_RESPONSE_CODE.

My outer stored procedure is collecting these pairs from all the inner stored procedure calls into the corresponding arrays, V_SUCCESSFUL_COUNTS and V_RESPONSE_CODES. First it opens the inner stored procedure's cursor, fetches the two parts into variables, and then adds those variables to the arrays.

PROCEDURE OUTER_SP(PARAM1, .., P_REF_CURSOR REF CURSOR)
AS
    ..
    V_SUCCESSFUL_COUNT      NUMBER;
    V_RESPONSE_CODE         VARCHAR2(10);
    V_SUCCESSFUL_COUNTS     REF_TYPE_NUM_TABLE := REF_TYPE_NUM_TABLE(); /* Cumulative Successful Counts */
    V_RESPONSE_CODES        REF_VARCHAR2_ARRAY_TABLE := REF_VARCHAR2_ARRAY_TABLE(); /* Cumulative Response Codes */
    V_REF_CURSOR            SYS_REFCURSOR; /* Result from each inner SP */

    FOR i IN 1 .. P_REF_ID_LIST.COUNT LOOP
                    SP_INNER(PARAM1,
                             PARAM2,
                             ...
                             V_REF_CURSOR);

        /* Add V_SUCCESSFUL_COUNT, V_RESPONSE_CODE outputs from this call to collections */
        OPEN V_REF_CURSOR /* Error here! */;
        FETCH V_REF_CURSOR INTO V_SUCCESSFUL_COUNT, V_RESPONSE_CODE;
        V_SUCCESSFUL_COUNTS.EXTEND;
        V_SUCCESSFUL_COUNTS(V_SUCCESSFUL_COUNTS.COUNT) := V_SUCCESSFUL_COUNT;
        V_RESPONSE_CODES.EXTEND;
        V_RESPONSE_CODES(V_RESPONSE_CODES.COUNT) := V_RESPONSE_CODE;
        CLOSE V_REF_CURSOR /* Error here! */;
    END LOOP;

I'm getting this error on the lines

OPEN V_REF_CURSOR / CLOSE V_REF_CURSOR

as I try to retrieve the result from each inner stored procedure call:

PLS-00382: expression is of wrong type

It doesn't like me opening the inner stored procedure's cursor before calling FETCH V_REF_CURSOR.

If I remove these OPEN V_REF_CURSOR / CLOSE V_REF_CURSOR lines and go straight to FETCH V_REF_CURSOR, there are no syntax error and the stored procedure compiles, but the results added into the array are incorrect (NULL), as I can see when I run this outer stored procedure.

Answer 1

If you are using a SYS_REFCURSOR data-type then you have probably used OPEN v_ref_cursor FOR ... in SP_INNER, in which case it is syntactically invalid to try to OPEN the cursor again as it is already open.

Remove the OPEN v_refcursor; statement (but not the CLOSE statement) and the code appears to work:

CREATE PACKAGE package_name AS
  TYPE REF_TYPE_NUM_TABLE IS TABLE OF NUMBER;
  TYPE REF_VARCHAR2_ARRAY_TABLE IS TABLE OF VARCHAR2(1);

  PROCEDURE SP_INNER(
    PARAM1 NUMBER,
    PARAM2 NUMBER,
    V_REF_CURSOR OUT SYS_REFCURSOR
  );

  PROCEDURE OUTER_SP(
    PARAM1 NUMBER,
    PARAM2 NUMBER,
    P_REF_ID_LIST SYS.ODCINUMBERLIST
  );
END;
/

CREATE PACKAGE BODY package_name AS
  PROCEDURE SP_INNER(
    PARAM1 NUMBER,
    PARAM2 NUMBER,
    V_REF_CURSOR OUT SYS_REFCURSOR
  )
  IS
  BEGIN
    OPEN v_ref_cursor FOR
      SELECT 1, 'A' FROM DUAL;
  END;

  PROCEDURE OUTER_SP(
    PARAM1 NUMBER,
    PARAM2 NUMBER,
    P_REF_ID_LIST SYS.ODCINUMBERLIST
  )
  AS
    V_SUCCESSFUL_COUNT      NUMBER;
    V_RESPONSE_CODE         VARCHAR2(10);
    V_SUCCESSFUL_COUNTS     REF_TYPE_NUM_TABLE := REF_TYPE_NUM_TABLE(); /* Cumulative Successful Counts */
    V_RESPONSE_CODES        REF_VARCHAR2_ARRAY_TABLE := REF_VARCHAR2_ARRAY_TABLE(); /* Cumulative Response Codes */
    V_REF_CURSOR            SYS_REFCURSOR; /* Result from each inner SP */
  BEGIN
    FOR i IN 1 .. P_REF_ID_LIST.COUNT LOOP
       SP_INNER(PARAM1, PARAM2, V_REF_CURSOR);
       /* Add V_SUCCESSFUL_COUNT, V_RESPONSE_CODE outputs from this call to collections */
       FETCH V_REF_CURSOR INTO V_SUCCESSFUL_COUNT, V_RESPONSE_CODE;
       V_SUCCESSFUL_COUNTS.EXTEND;
       V_SUCCESSFUL_COUNTS(V_SUCCESSFUL_COUNTS.COUNT) := V_SUCCESSFUL_COUNT;
       V_RESPONSE_CODES.EXTEND;
       V_RESPONSE_CODES(V_RESPONSE_CODES.COUNT) := V_RESPONSE_CODE;
       CLOSE V_REF_CURSOR /* Error here! */;
    END LOOP;

    FOR i IN 1 .. V_SUCCESSFUL_COUNTS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(V_SUCCESSFUL_COUNTS(i) || ' - ' || V_RESPONSE_CODES(i));
    END LOOP;
  END;
END;
/

Then:

BEGIN
  DBMS_OUTPUT.ENABLE();
  package_name.OUTER_SP(3.141, 42, SYS.ODCINUMBERLIST(1,2,3));
END;
/

Outputs:

1 - A
1 - A
1 - A

fiddle