CODEWITHSUNDEEP
oracle-databaseplsql
swet
swet

Reputation: 217

DBMS_LOCK throwing error in procedure ie identifier must be declared

When I execute "dbms_lock.sleep", it runs successfully. However, when I put the it in a procedure then it throws error like 'identifier 'DBMS_LOCK must be declared''

exec dbms_lock.sleep ( 5 ); -- this is working 

create procedure p1
...
dbms_lock.sleep ( 5 ); -- Error(264,2): PLS-00201: identifier 'DBMS_LOCK' must be declared
...
end;
/

The below is from dba_objects:

SYS     DBMS_LOCK   PACKAGE BODY
SYS     DBMS_LOCK   PACKAGE
PUBLIC  DBMS_LOCK   SYNONYM
SYS     DBMS_OUTPUT PACKAGE
PUBLIC  DBMS_OUTPUT SYNONYM
SYS     DBMS_OUTPUT PACKAGE BODY

Upvotes: 2

Views: 3887

Answers (1)

user330315
user330315

Reputation:

Privileges obtained through a role are not effective inside a PL/SQL block. The execute privilege on the package needs to be granted to the user directly, e.g.:

grant execute on dbms_lock to your_oracle_user;

Upvotes: 4

Related Questions