SA-3361

Summary

User has noticed stored procedure - SPPR_SYS_RESERVEQUEUEITEM_B getting called very frequently in their environment. The database administrator have noticed that cursors were left open when there was an exception raised.  

Error Messages

No error message

Steps to Reproduce

Root Cause

Whenever an exception occurs like NO_DATA_FOUND for the stored procedure SPPR_SYS_RESERVEQUEUEITEM_B, cursors were not closed.

EXCEPTION
WHEN NO_DATA_FOUND THEN
InsKey := null;
ROLLBACK;
RETURN;
WHEN nowait_lock THEN
InsKey := null;
RETURN;
WHEN noserial THEN
InsKey := null;
ROLLBACK;
RETURN;
WHEN OTHERS THEN
InsKey := null;
ROLLBACK;
RAISE;

EXCEPTION
WHEN NO_DATA_FOUND THEN
InsKey := NULL;
WHEN OTHERS THEN
InsKey := NULL;
RAISE;
END;

Resolution

Report issue was resolved using local change - updated stored procedure script SPPR_SYS_RESERVEQUEUEITEM_B.SQL with following code to close the cursor, if the exception occurs.
 
WHEN NO_DATA_FOUND THEN
InsKey := null;
ROLLBACK;
IF insKeyCurVar%ISOPEN THEN
CLOSE insKeyCurVar;
END IF;
RETURN;
WHEN nowait_lock THEN
InsKey := null;
IF insKeyCurVar%ISOPEN THEN
CLOSE insKeyCurVar;
END IF;
RETURN;
WHEN noserial THEN
InsKey := null;
ROLLBACK;
IF insKeyCurVar%ISOPEN THEN
CLOSE insKeyCurVar;
END IF;
RETURN;
WHEN OTHERS THEN
InsKey := null;
ROLLBACK;
IF insKeyCurVar%ISOPEN THEN
CLOSE insKeyCurVar;
END IF;
RAISE;