2 sessions executing same procedure same time

Hi,
How can i prevent 2 sessions executing a procedure at a time? Requirement is thet when the package is still running, it should be run again.

dont get your question, sounds contradictory

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?

By proc, i mean stored procedure, also in 1st posting, i mentioned package, but take it as procedure....

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.

I don't like that use of tables to serialize access to a resource -- this is exectly what the DBMS_LOCK package is intended for.

Have a look at the Supplied PL/SQL Packages and Types Reference for details.

That's the way to do it. I guess I have seen it before, tahiti search on "enqueue resource" only finds the DML_LOCKS parameter . . . (lame excuse).

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

I'm sure that they don't survive. (yeah,i see where you're going with this, Mr Smarty Pants)

HO HO HO !

(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).