PLS-00382: “a expressão é do tipo errado” ao abrir REF CURSOR em um procedimento armazenado chamado em um loop

Se você estiver usando um SYS_REFCURSORtipo de dados, provavelmente já usou OPEN v_ref_cursor FOR ...in SP_INNER; nesse caso, é sintaticamente inválido tentar OPENo cursor novamente, pois ele já está aberto.

Remova a OPEN v_refcursor;instrução (mas não a CLOSEinstrução) e o código parece funcionar:

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

Então:

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

Saídas:

1 - A
1 - A
1 - A

violino

Eu suspeito que SP_INNERnão retorna nada.

Aqui está uma demonstração que mostra como fazer isso (se tudo correr bem).

Esta é uma consulta que retorna alguns dados; é usado em SP_INNER:

SQL> select ename, sal from emp where deptno = 10;

ENAME             SAL
---------- ----------
CLARK            2450
KING             5000
MILLER           1300

Meu SP_INNERprocedimento é muito simples; a única coisa "importante" aqui é que a consulta usada para um cursor ref realmente retorna algo:

SQL> create or replace procedure sp_inner(par_rc out sys_refcursor)
  2  is
  3  begin
  4    open par_rc for
  5      select ename, sal from emp where deptno = 10;
  6  end;
  7  /

Procedure created.

SQL> set serveroutput on;

Bloco PL/SQL anônimo que simula seu OUTER_SP:

SQL> declare
  2    l_rc    sys_refcursor;
  3    l_ename emp.ename%type;
  4    l_sal   emp.sal%type;
  5  begin
  6    sp_inner(l_rc);
  7    loop
  8      fetch l_rc into l_ename, l_sal;
  9      exit when l_rc%notfound;
 10      dbms_output.put_line(l_ename||' - '||l_sal);
 11    end loop;
 12  end;
 13  /
CLARK - 2450
KING - 5000
MILLER - 1300

PL/SQL procedure successfully completed.

SQL>

Como você pode ver, a saída reflete o que selecta própria instrução retorna, então funciona .

Como você não recebeu nada, verifique o SP_INNERque está acontecendo.

Além disso, foro loop usa P_REF_ID_LIST.COUNT, mas você nunca disse o que p_ref_id_listé/contém. Se não houver nada lá, esse loop não será executado, então esse pode ser outro culpado. Exiba P_REF_ID_LIST.COUNTo valor antes de usá-lo no loop para verificar seu valor.