CODEWITHSUNDEEP
oracle-databaseprocedure
UltraCommit
UltraCommit

Reputation: 2276

Oracle PL/SQL: How to detect if a procedure is ALREADY running?

Please suppose that we have a procedure inside a package:

MY_PACKAGE.MY_PROCEDURE

This procedure could be launched from many users.

How can I modify the procedure in order to detect if the procedure is at present running since launched from another user?

What is the safest way to detect it?

Thank you for considering my request.

EDIT 01: "It'll depend on why you need to know if a proc is already running or not" ==> If the procedure is at present running, it WON'T be launched again.

Upvotes: 1

Views: 8196

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

You can use the DBMS_APPLICATION_INFO package for such information.

PROCEDURE MY_PROCEDURE(..) IS
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO('MY_PACKAGE.MY_PROCEDURE running');

   ... All your stuff

DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL);
    RAISE;
END MY_PROCEDURE;

In order to check it, you can select V$SESSION View:

SELECT * 
FROM v$session 
WHERE client_info = 'MY_PACKAGE.MY_PROCEDURE running';

If you get any records then the procedure is running.

Upvotes: 3

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Based on what others have mentioned and a quick perusal of the DBMS_LOCK package header it appears that you can use the various DBMS_LOCK routines to accomplish what you're trying to do. If I'm reading the header comments correctly you'd want to call ALLOCATE_UNIQUE to get a handle to a unique, named lock, then you'd call REQUEST with the locking mode set to 'x' (Exclusive) to try to grab the lock. If the REQUEST call returns 0 you can go ahead and run your routine. When done, call RELEASE to make the lock available to the next caller.

Best of luck.

Upvotes: 2

Related Questions