Re: Exclusive (non-reentrant) Stored Procedure

In article <92alie$li7$1_at_nnrp1.deja.com>,   dejaschirf_at_my-deja.com wrote:
> I need to create a stored procedure that is, in effect, not reentrant.
> What I’d like to have happen is for any calls to the stored procedure
> which occur while it is already running to immediately return as if
> they had run successfully. I’ve looked for a semaphore mechanism in
> Oracle but have had no luck finding a way to create a resource lock
> that will unlock automatically on unexpected termination.
>
> Can anyone help me with this?
>
> Paul Schirf
> Frustrated Oracle Developer
>
> Sent via Deja.com
> http://www.deja.com/
>

You are correct. There are no semaphores or similar mechanism in PL/SQL. Packages can have global variable that persist for the duration that the database is open. Not the best plan though as one could envision two user almost simultaneously entering a value.

The obvious solution is to build a small table with one row. Oracle would prevent two or more users from simultaneously updating the same row eg

           update <table>
                set <column> = 1
                where <column> != 1

I built a similar scheme to mediate access to a data warehouse. Basically, each external database marked a row prior to writting to the data warehouse if and only if there was less than 5 other writers.

This was reliable unless the external database failed -- like during a power failure. Then the locks were incorrectly set.

Back to the original method -- if the update were part of a transaction and/or all exits to the procedure were set with an appropriate command t reverse the update, this should work.

You could add other criteria like a time stamp, to override the update not being cleared. Or use the user id to make sure that the user did the update

            update <table>
                set <column1> = userid,
                    <column2> = sysdate
                where
                    <column1> is null
                    OR sysdate - <column2> > 0.1    -- 2.4 hours

     or use 'select for update no wait' to test if null then update.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/