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?
You can do this with
See the following procedure:
Test (session 1):
(Obviously returns when
Test (session 2):
Obviously you need to
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.
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.