Re: serializing access to a stored procedure

Hi John,

The simplest option is to create a single row table that is locked exclusively with a NOWAIT option right at the beginning of the procedure and is released whenever the need for serializing is complete. The procedure can so be coded so that it looks at the v$session / v$process views to detect who is holding the lock and can thus inform the second user. This way, when one user is using the procedure, the next one in line is aware of who has the lock... Unfortunately, if a transaction is aborted, you could be left with a bogus lock - you will need some manual intervention to clear it. Implementation of User profiles can help greatly to reduce such bogus locks, btw.

Another option is to use DBMS_PIPE which can send messages to another process that will process the info serially.

If you are using Oracle8, you could always play around with the Advanced Queuing Option - detailed in the Apps Dev Guide.

Hope this helps,

John Kanagaraj
Brunei

john kelly <johnt.kelly_at_worldnet.att.net> wrote in message news:37fa939a.3192831_at_netnews.worldnet.att.net...
> Is there a way to serializing access to a stored procedure?
> I have a production environment where we call a stored procedure to
> process new transactions. The only problem is that if two or
> processes call this procedure I will have problems with transaction
> being doubly processed or lost. There's a lot of code in this
> procedure and it will take me a long time to break it out and rewrite
> it. I was hoping that a general lock approach can be used internally
> (to the stored procedure) and I wont have to worry about who/when is
> calling it.
>
> Thanks for your help.
>
> John
Received on Sun Oct 03 1999 - 08:45:04 CDT