Support Article
SPPR_SYS_RESERVEQUEUEITEM_B Stored Proc leave cursors open
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;
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.