Re: How to ensure only one session runs PL/SQL proc at a time?

Are you running version 8.1 ?

Create a package with a procedure to acquire the unique value and store it in a private variable; and a function to return the value.

Create a database logon trigger to call the procedure to set the value.

In your trigger, call your packaged procedure rather than the dbms_lock function.

In earlier versions it's probably more appropriate to create a table of 'application lock names'. Populate it with lock names and call the dbms_lock function for each row to get a lock number. Then your application trigger can read the table by name to acquire the number.

--
Jonathan Lewis

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

For latest news of public appearances
See http://www.jlcomp.demon.co.uk

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.




Ian Ledzion wrote in message <9m507a$ge3$1_at_rex.ip-plus.net>...


>You're right about the constant, but it's just that a numeric constant is

>less easy to manage than a string.  allocate_unique returns a number (which

>will be sent to the request and release functions) from a string.

>

>Would you rather manage a lock number 10737418251073741825148 or its string

>source, 'FOO'?

>

>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

>news:998635782.28060.0.nnrp-02.9e984b29_at_news.demon.co.uk...

>>

>> Why do you need allocate unique ?

>>

>> If you want to allow just one copy of the

>> procedure to run at a time, shouldn't you have

>> a constant associated with the procedure ?

>>

>> --

>> Jonathan Lewis