Hi,
We're using Oracle 12c.
I asked this question badly last time so I'm going to try to re-ask it more clearly.
Passing a cursor from a procedure. Trying to test print its contents.
I'm putting all information below including Cursor Definition, T_SLD_GIC_REPO_NONREPO_REC Table definition,
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;
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
Below is the definition of the cursor:
OPEN po_ref_cur FOR
SELECT c_run_type
|| ','
|| TO_CHAR(d_inc_dt, 'DD/MM/YYYY')
|| ','
|| lend_agnt
|| ','
|| trim(ACCNT)
|| ','
|| trim(PORTFOLIO)
|| ','
|| PROG_TYPE
|| ','
|| NVL(trim(DAY_CT), '<NULL>') -- DAY_CT
|| ','
|| NVL(trim(REPOCP_LEI_CODE), '<NULL>') -- REPOCP_LEI_CODE
|| ','
|| NVL(trim(REPOCP_BR_DESC), '<NULL>') -- REPOCP_BR_DESC
FROM t_sld_gic_repo_nonrepo_rec
ORDER BY c_run_type,d_inc_dt, NVL(issuer_repocp, 'ZZ');
Also here is the definition of the table the %ROWTYPE; is using.
CREATE GLOBAL TEMPORARY TABLE SLDPROC.T_SLD_GIC_REPO_NONREPO_REC
(
D_INC_DT DATE,
LEND_AGNT VARCHAR2(2 BYTE),
I_LOAN_NUM_REF VARCHAR2(6 BYTE),
ACCNT VARCHAR2(8 BYTE),
PORTFOLIO VARCHAR2(8 BYTE),
PROG_TYPE VARCHAR2(2 BYTE),
DAY_CT VARCHAR2(100 BYTE),
REPOCP_LEI_CODE VARCHAR2(256 BYTE),
REPOCP_BR_DESC VARCHAR2(300 BYTE)
)
ON COMMIT DELETE ROWS
NOCACHE;