PLS-00201
PLS-00201 means that the identifier you specified in the statement has never been declared, so it cannot be used by the stored procedure.
In this post, we'll talk about some error patterns of PLS-00201.
Undeclared Variable
What is undeclared variable? Let's see an example to make it clear.
SQL> set serveroutput on;
SQL> begin
2 select first_name into v_fn from employees where last_name = 'Rowe';
3 dbms_output.put_line('The first name is: ' || v_fn);
4 end;
5 /
select first_name into v_fn from employees where last_name = 'Rowe';
*
ERROR at line 2:
ORA-06550: line 2, column 26:
PLS-00201: identifier 'V_FN' must be declared
ORA-06550: line 2, column 31:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 3, column 49:
PLS-00201: identifier 'V_FN' must be declared
ORA-06550: line 3, column 3:
PL/SQL: Statement ignored
In the example, it found an identifier which is not declare anywhere in the programming unit.
Solution
In fact, the identifier is a local variable, we just forgot to declare it before using. Let's declare the variable as a string.
SQL> declare
2 v_fn varchar2(25);
3 begin
4 select first_name into v_fn from employees where last_name = 'Rowe';
5 dbms_output.put_line('The first name is: ' || v_fn);
6 end;
7 /
The first name is: John
PL/SQL procedure successfully completed.
The final result has been successful output.
DBMS_SQL
Some SYS's packages are very common to PUBLIC to EXECUTE, such as DBMS_SQL, DBMS_LOB or UTL_FILE.
PLS-00201: identifier 'DBMS_SQL' must be declared
Solution
In such case, the right privileges may be gone or revoked from PUBLIC. I have provided the solution in the post: How to Resolve PLS-00201: identifier 'DBMS_SQL' must be declared.
DBMS_LOCK
DBMS_LOCK does not open to PUBLIC, it should be granted to specific user to execute whenever required.
SQL> begin
2 dbms_lock.sleep(10);
3 end;
4 /
dbms_lock.sleep(10);
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
Solution
We should grant EXECUTE privilege on the package to the user by SYS.
SQL> show user
USER is "SYS"
SQL> grant execute on dbms_lock to hr;
Grant succeeded.
Then we run the programming unit again.
SQL> begin
2 dbms_lock.sleep(10);
3 end;
4 /
PL/SQL procedure successfully completed.