DB version: 11.2.0.4
I have a production PL/SQL stored procedure which has several Cursors in it. This stored procedure doesn't have any DML in it. ie No DELETE, UPDATE, INSERT or MERGE. Just SELECT queries and no COMMITs.
For the last one year, I am gettting the following ORA-01002 error message in the application logs quite frequently.
Error retrieving the value of a parameter CUR_CUST1.
An error occurred when retrieving the value of parameter CUR_CUST1 after invoking the TEMS.SKU_BRIDGE API.
Cause: java.sql.SQLException: ORA-01002: fetch out of sequence
The stored procedure code looks like below . Including only the relevant parts.
This stored proc code has around 1000 lines in it. But, CUR_CUST1 cursor is referred only twice in the entire code. I have reddened those 2 lines. As you can see, CUR_CUST1 is OPENed once, but never actually used. Is it safe to remove the lines in red just to see if the error goes away ?
CREATE OR REPLACE PROCEDURE SKU_BRIDGE
(
SkuRowId IN VARCHAR2,
SkuID IN VARCHAR2,
SkuIDType IN VARCHAR2,
MSISDN IN VARCHAR2,
KENANAccountID IN VARCHAR2,
CUR_CUST1 OUT TYPES.CUR_INBOUNDQUERY_TYPE,
StatusCode OUT VARCHAR2,
ErrorCode OUT VARCHAR2,
ErrorMessage OUT VARCHAR2,
V_SQL_TRACE OUT VARCHAR2
) IS
.
.
.
CURSOR C1 IS SELECT ........ ;
CURSOR C2 IS SELECT ......... ;
CURSOR C3 IS SELECT ......... ;
OPEN CUR_CUST1 FOR V_SQL USING SkuRowId, SkuID, SkuIDType, MSISDN, KENANAccountID;
OPEN CUR_CUST1_COUNT FOR V_SQL_CHK USING SkuRowId, SkuID, SkuIDType, MSISDN, KENANAccountID;
FETCH CUR_CUST1_COUNT INTO V_SQL_REC_CHK;
V_REC_CNT := CUR_CUST1_COUNT%ROWCOUNT;
CLOSE CUR_CUST1_COUNT;
.
.
.
.
EXCEPTION
WHEN OTHERS THEN
StatusCode := 'NOTOK';
ErrorCode := V_ERR_91;
SQL_ERR := SUBSTR(SQLERRM,1,2000);
SELECT TEXT||SQL_ERR
INTO ErrorMessage FROM TEMS.S_ERR_MSG_INTL MSG_INTL, TEMS.S_ERR_MSG MSG , TEMS.S_REPOSITORY REPO
WHERE MSG.ROW_ID = MSG_INTL.ERR_MSG_ID
AND MSG.INACTIVE_FLG = 'N'
AND MSG.REPOSITORY_ID = REPO.ROW_ID
AND REPO.NAME ='Tems Repository'
AND MSG.NAME = ErrorCode;
END SKU_BRIDGE;
/