Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

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

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice