Hi All,
I would like to restrict concurrent executions of a procedure. In other words, would like to serialize it. Simple enough, I can use locking mechanism for this but this procedure has an out parameter which is a ref cursor and the lock will eventually be released as soon as the ref cursor is opened and the control reaches the end of the procedure but I would like is to restrict the users from not being able to execute this procedure again until that ref cursor is closed. Is there a way that this can be achieved from with in Oracle DB?
Sample Code:
CREATE OR REPLACE PROCEDURE SH.SALES_BY_PRODUCT(P OUT SYS_REFCURSOR) IS
BEGIN
..... --Lock here
OPEN P FOR
SELECT PROD_ID, SUM(AMOUNT_SOLD) AS AMOUNT FROM SH.SALES GROUP BY PROD_ID;
..... --Release the lock here --This approach of locking is not helping as the lock is released even before the cursor P is closed by the calling application.
END;
Thanks!