In an application using Oracle database DBMS_LOCK procedure , sometimes if the grant permissions are not provided it may result in below errors
Error observed : PLS-00201
PLS-00201: identifier 'SYS.DBMS_LOCK' must be declared
Solution :
Reason : DBMS Lock package is not declared
- Login to DB schema
- Execute the oracle default procedure $ORACLE_HOME/rdbms/admin/dbmslock.sql
- @$ORACLE_HOME/rdbms/admin/dbmslock.sql
Error observed : PLS-00221
SQL> exec
DBMS_LOCK();
BEGIN DBMS_LOCK();
END;
*
ERROR at line 1:
ORA-06550: line 1,
column 7:
PLS-00221:
'DBMS_LOCK' is not a procedure or is undefined
ORA-06550: line 1,
column 7:
PL/SQL: Statement
ignored
Solution :
Reason : Schema user may not be having permission to use DBMS Lock package
- Login to DB schema
- Execute the grant query to provide permission access to DBMS lock package
- Syntax : GRANT EXECUTE on dbms_lock to <dbuser>;
- Example : GRANT EXECUTE on dbms_lock to shareolite;
Hope it helps to some beginners.