Error passing an Oracle cursor when trying to test print its contents-CodePudding

We're using Oracle 12c.

Passing a cursor from a procedure. Trying to test print its contents.

I get the following error message:

ORA-01001: invalid cursor

ORA-06512: at line 25

Why is the cursor invalid?

Here's the code.

SET SERVEROUTPUT ON
--VARIABLE X REFCURSOR;
DECLARE
  RUN_DATE       VARCHAR2 (10);
  D_XMAS_NY      VARCHAR2 (10);
  PO_ERROR_CODE_N   NUMBER;
  PO_ERROR_MESSAGE_C  VARCHAR2 (32767);
  PO_REF_CUR      SLD_COMMON_PKG.PG_COMMON_REFCUR;
  
  V_VAL        SLDPROC.t_sld_gic_repo_nonrepo_rec%ROWTYPE;
  
BEGIN
  RUN_DATE := '2022-07-27';
  D_XMAS_NY := '9999-12-30';
  PO_ERROR_CODE_N := NULL;
  PO_ERROR_MESSAGE_C := NULL;
  
--  PO_REF_CUR := NULL;

  SLDPROC.SP_SLD_GEN_GIC_REINV_DET (RUN_DATE,
                    D_XMAS_NY,
                    PO_ERROR_CODE_N ,
                    PO_ERROR_MESSAGE_C,
                    PO_REF_CUR);

   LOOP   
     FETCH PO_REF_CUR INTO V_VAL;
      
   EXIT WHEN PO_REF_CUR%NOTFOUND;      
       
/*Notice the DBMS_OUTPUT line is commented out. So at this point, Oracle is just running through the cursor.*/
         --DBMS_OUTPUT.PUT_LINE(  V_VAL.d_inc_dt );
                    
   END LOOP;    
  CLOSE PO_REF_CUR;

END;

I get the following error message:

ORA-01001: invalid cursor
ORA-06512: at line 25

The Procedure SLDPROC.SP_SLD_GEN_GIC_REINV_DET compiles correctly and the cursor inside the procedure is correct. Finally when I run this procedure without any trace of the Cursor Loop it finishes correctly. It's when I try to list out the contents of the cursor.

The contents of the cursor consist of 1 giant column with all the columns from a table concatenated together like this.

   OPEN po_ref_cur FOR
   SELECT      c_run_type
             || ','
             || TO_CHAR(d_inc_dt, 'DD/MM/YYYY')
             || ','
             || lend_agnt
             || ','
             || trim(ACCNT)
             || ','
             || NVL(trim(DAY_CT), '<NULL>')     -- DAY_CT                                
             || ','
             || NVL(trim(REPOCP_LEI_CODE), '<NULL>')     -- REPOCP_LEI_CODE
             || ','
             || NVL(trim(REPOCP_BR_DESC), '<NULL>')  
                     FROM t_sld_gic_repo_nonrepo_rec
      ORDER BY c_run_type,d_inc_dt, NVL(issuer_repocp, 'ZZ');

CodePudding user response：

SP_SLD_GEN_GIC_REINV_DET procedure's last parameter should be OUT, returning a ref cursor. Is it?

Because, if I try to mimic what you did, that code works.

Sample procedure:

SQL> CREATE OR REPLACE PROCEDURE SP_SLD_GEN_GIC_REINV_DET (
  2      par_rc OUT SYS_REFCURSOR
  3  ) IS
  4  BEGIN
  5      OPEN par_rc FOR SELECT dname FROM dept;
  6  END;
  7  /

Procedure created.

SQL>

Your anonymous PL/SQL block (slightly modified):

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      run_date           VARCHAR2(10);
  3      d_xmas_ny          VARCHAR2(10);
  4      po_error_code_n    NUMBER;
  5      po_error_message_c VARCHAR2(32767);
  6      po_ref_cur         SYS_REFCURSOR;-- SLD_COMMON_PKG.PG_COMMON_REFCUR;
  7      v_val              VARCHAR2(200); --SLDPROC.t_sld_gic_repo_nonrepo_rec%ROWTYPE;
  8  BEGIN
  9      run_date := '2022-07-27';
 10      d_xmas_ny := '9999-12-30';
 11      po_error_code_n := NULL;
 12      po_error_message_c := NULL;
 13
 14  --  PO_REF_CUR := NULL;
 15  --  SLDPROC.SP_SLD_GEN_GIC_REINV_DET (RUN_DATE,
 16  --                    D_XMAS_NY,
 17  --                    PO_ERROR_CODE_N ,
 18  --                    PO_ERROR_MESSAGE_C,
 19  --                    PO_REF_CUR);
 20      SP_SLD_GEN_GIC_REINV_DET (po_ref_cur);
 21      LOOP
 22          FETCH po_ref_cur INTO v_val;
 23          EXIT WHEN po_ref_cur%notfound;
 24          /*Notice the DBMS_OUTPUT line is commented out. So at this point, Oracle is just running through the cursor.*/
 25          DBMS_OUTPUT.PUT_LINE(V_VAL);
 26      END LOOP;
 27      CLOSE po_ref_cur;
 28  END;
 29  /
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.

SQL>