2 sessions executing same procedure same time

[slimdave]

How are you going to distinguish between the two cases of "the procedure is already running", and "the procedure failed"? It seems like the table-based method provides no methodology for this.

how about this ...

Use DBMS_LOCK to serialize the procedure.
Use table-row to indicate that the procedure has been started and not finished.

The table part may not be required, if the procedure uses temporary tables and commits only after complete units of work have been completed, as it would be "self-cleaning" if the procedure failed, or instance shutdown/crashed.

I suppose after a while it starts depending on the complexity of the process. I'd still use DBMS_LOCK to indicate "process already running", though.

[DaPi]

Originally posted by slimdave
How are you going to distinguish between the two cases of "the procedure is already running", and "the procedure failed"? It seems like the table-based method provides no methodology for this.

Point taken. Thanks.

(The process that is occupying my mind at the moment is a great usine Ã* gaz where a unit of work includes SQL, OS scripts and some Access - I'll keep on using the table as well.)

P.S. Come to think of it the orignal problem in this thread will need the table since UTL_FILE output won't be rolled back.

[slimdave]

Maybe a virtue could be made out of necessity by having a table to log the procedure activity ... indicating start time, end time, input parameters, results (rows written etc).

That would probably be the kind of thing you'd want to include anyway, and it could also be checked at the start of the procedure to see if cleanup from a prior run is required.

My personal usine Ã* gaz was a wrapper for a c-based EDI extract program -- shell scripts invoking perl to check SQL-spooled results of PL/SQL-built tables to validate the output of the C program. Don't forget to not document anything -- this is potentially your retirement fund that you're working on.

[DaPi]

Originally posted by slimdave
Don't forget to not document anything -- this is potentially your retirement fund that you're working on.

Now you really are teaching gandpa to suck eggs

[slimdave]

Originally posted by DaPi
Now you really are teaching gandpa to suck eggs

Sorry -- just proselytising to the newbies.

[manodba]

Hi, back after weekend. Thanx for all the suggestions.
Procedural options always exist....like one of those interesting one slimdave had suggested! but i wonder why there is no before execute trigger? any ideas....

News::I am Certified Oracle 8i DBA from yesterday!!!