Re: DBMS_LOCK.SLEEP

bigjobbies_at_hotmail.com (Ethel Aardvark) wrote in message news:<1a8fec49.0306110819.6e735f88_at_posting.google.com>...
> I am running Oracle 9.0.1.3.1 on W2K and dbms_lock.sleep() is
> confusing me.
>
> If I log in to SQL*Plus and type:
>
> BEGIN
> dbms_lock.sleep(5);
> END;
>
> ...I get a 5 second delay.
>
> If, however, I put the same block into a procedure:
>
> CREATE OR REPLACE PROCEDURE play IS
> BEGIN
> dbms_lock.sleep(5);
> END play;
>
> ...it refuses to compile, claiming that it "...identifier 'DBMS_LOCK'
> must be declared".
>
> Why is this?
>
> My gut feeling is that it is an invoker/definer rights issue but not
> one that I can see (adding "AUTHID CURRENT_USER" or "AUTHID DEFINER"
> makes no apparent difference).
>
> Please can someone help me - it's driving me mad!
>
> (By the way, changing to "USER_LOCK.SLEEP(500);" works fine.)
>
> Thanks in advance,
>
>
> ETA
authid would need to have been used on the definition of dbms_lock of course.
Something which you shouldn't change.
No other solution than grant execute on dbms_lock to <user calling the procedure > *directly*

You aren't routinely working as SYS aren't you? You shouldn't do that.

Sybrand Bakker
Senior Oracle DBA Received on Thu Jun 12 2003 - 09:31:24 CEST