Will exlpain more clearly...
Well, one of my procs writes to a OS file using utl_file and will rename the file at the end. But if the proc is called again while it is still running, the file gets overwritten.
I am wondering if there is any way to ensure that a particular proc will not be run while it is still running.
One more question, Is there any BEFORE EXECUTE trigger?
One way to allow everyone to execute at the same time could be to use unique file names e.g. generated fom the audit sid (sys_context('USERENV','SESSIONID')). These could then be concatenated at the OS level.
If that can't be arranged then you need to create a 'resource' that you can 'enqueue' on - this could be just a row in a table with values 'BUSY'/'FREE' that is tested (with locking) and updated at the start and then reset at the end of your proc.
slimdave (since you seem to know about these things), am I right in assuming that locks set by DBMS_LOCK don't survive shutdown/startup or crash/recovery? (couldn't find it explicitly stated in the doc). TIA
(To spell it out for anyone who doesn't use ESP v7.2) So if I use the DBMS_LOCK, I'll still want maintain a status table for "what the hell was going on" post-crash diagnosis. In which case . . . . why not enqueue on a row in that table?
slimdave, what don't you like about that method? (the European jury is out on this one - no axe to grind).