dbms_lock.sleep

cooldude87801
cooldude87801 used Ask the Experts™
on
Hi,
    I am trying to use the dbms_lock.sleep comman in a procedure like this for example :

 create or replace procedure testing as

begin

  dbms_output.put_line('we gonna go to sleep for 120 secs');
  dbms_lock.sleep(120);
  dbms_output.put_line('Rise and Shine');

end testing;
/


and I end up getting this errors :

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3      PLS-00201: identifier 'SYS.DBMS_LOCK' must be declared
6/3      PL/SQL: Statement ignored

What's that supposed to mean ? and what should I do to solve this problem....I would appreciate any suggestions or comments..it is very urgent!!!!

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
check if you have permissions to execute the procedure dbms_lock.sleep();

if not give execute permissions to the user who's executing this procedure and try again...
PLS-00201 identifier 'string' must be declared

Cause:
An attempt was made to reference either an undeclared variable, exception, procedure, or other item, or an item to which no privilege was granted or an item to which privilege was granted only through a role.

Action:
Check your spelling and declaration of the referenced name.

Verify that the declaration for the referenced item is placed correctly in the block structure.

If the referenced item is indeed declared but you do not have privileges to refer to that item, for security reasons, you will be notified only that the item is not declared.

If the referenced item is indeed declared and you believe that you have privileges to refer to that item, check the privileges; if the privileges were granted only via a role, then this is expected and documented behavior.

Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared.

Author

Commented:
How can I check if I have permissions to execute it or not ? And if I don't have a permission to execute it myself how can I grant it for someone else?

thanks
Do you have DBA access? if you do then lof on as system and execute "grant execute on dbms_lock to <user>". If you don't have DBA access, then ask your DBA to grant this to your user.

Commented:
Perhaps you do not have the DBMS_LOCK package installed at all:

Log in to the database as SYS and do "Desc DBMS_LOCK".

If it is found then then do "Grant Execute On DBMS_LOCK To <User>" where <User> is the schema creating the procdure.

If it is not found then as SYS run the script dbmslock.sql in the <Oracle Home>\rdbms\admin directory and then perform the grant.

I hope that helps

Regards

Chedgey

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial