Skip to content

How to Resolve PLS-00201: identifier must be declared

  • by

PLS-00201

PLS-00201 means that the identifier you specified in the statement has never declared, so it cannot be used by the stored procedure.

In this post, we'll talk about some error patterns of PLS-00201.

  1. Undeclared Variable
  2. DBMS_SQL
  3. DBMS_LOCK

A. 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 = 'Chen';
  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 = 'Chen';
                         *
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 = 'Chen';
  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.

B. 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.

C. 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.

Leave a Reply

Your email address will not be published.