Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error.

Whats the best method of doing this?

share|improve this question
add comment

3 Answers

up vote 10 down vote accepted

You can do this with DBMS_LOCK and an exclusive lock.

See the following procedure:

CREATE OR REPLACE PROCEDURE myproc
IS
  lockhandle VARCHAR2(128);
  retcode NUMBER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('myproclock',lockhandle);

  retcode:=DBMS_LOCK.REQUEST(lockhandle,timeout=>0, lockmode=>DBMS_LOCK.x_mode);

  IF retcode<>0
  THEN
    raise_application_error(-20000,'myproc is already running');
  END IF;

  /* sleep so that we can test with a 2nd execution */
  DBMS_LOCK.sleep(1000);

  retcode:=DBMS_LOCK.RELEASE(lockhandle);

END myproc;
/

Test (session 1):

SQL> BEGIN
  2  myproc();
  3  END;
  4  /

(Obviously returns when DBMS_LOCK.sleep() returns).

Test (session 2):

SQL> BEGIN
  2  myproc();
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-20000: myproc is already running
ORA-06512: at "PHIL.MYPROC", line 12
ORA-06512: at line 2


SQL>

Obviously you need to GRANT EXECUTE ON DBMS_LOCK TO YOURUSER;.

share|improve this answer
add comment

When my clients have a request that has unique business logic like this one I try turning the question around and asking why this is needed.

Best way to make sure only one copy is running is not to let users have execute on the procedure at all. If this procedure is so special then it's use should be restricted to dba/developers.

Another way is to only run this procedure as a job. Add a check in the procedure to see if any jobs calling this are running. If they are then stop further processing and log the occurrence.

share|improve this answer
add comment

Use a 'lock' table.

When procedure starts check the table for a known value, if present go no further and exit proc. If not there, write the value to the table, execute procedure, then delete value and exit as normal.

share|improve this answer
    
This is a good solution for many systems, but in this special case Phil's suit better, I think. –  dezso Sep 13 '12 at 14:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.