How to Solve PLS-00201: Identifier must be declared

Oracle’s PL/SQL is a powerful procedural language that extends SQL with procedural capabilities, allowing developers to write complex business logic within the database. However, errors are inevitable when working with PL/SQL, and one such common error is PLS-00201: Identifier must be declared.

This error can be frustrating, especially for beginners, because it can be caused by multiple factors, including undeclared variables, missing privileges, or incorrectly referenced objects. In this comprehensive article, we will explore the causes of this error, discuss how to diagnose it, and provide solutions to fix it.

Understanding the Error

The PLS-00201 error occurs when a PL/SQL block tries to reference an identifier (such as a variable, constant, procedure, function, package, or table) that has not been declared or is not accessible in the given scope.

Error Message Format

PLS-00201: identifier '<name>' must be declared

The <name> part of the error message will specify the identifier that is causing the issue.

Example of the Error

Consider the following example where we attempt to call a procedure that has not been declared:

BEGIN
    my_procedure;
END;
/

Error Output:

PLS-00201: identifier 'MY_PROCEDURE' must be declared

Since my_procedure has not been defined in the session, the compiler throws an error.

Common Causes of PLS-00201 Error

Several issues can lead to this error. Let’s explore the most common ones:

1. The Identifier is Not Declared

If you try to use a variable, function, procedure, or package that has not been declared within the PL/SQL block, you will get this error.

Example

DECLARE
    v_salary NUMBER;
BEGIN
    v_salary := emp_salary;
END;
/

Solution

Ensure that the variable emp_salary is declared before use:

DECLARE
    emp_salary NUMBER;
    v_salary NUMBER;
BEGIN
    emp_salary := 5000;
    v_salary := emp_salary;
END;
/

2. Incorrect Object Name or Reference

If you misspell an object name or use an incorrect reference, Oracle will not be able to resolve the identifier.

Example

BEGIN
    dbms_output.putlin('Hello');  -- Typo in DBMS_OUTPUT.PUT_LINE
END;
/

Solution

Correct the typo:

BEGIN
    dbms_output.put_line('Hello');
END;
/

3. The Identifier is Declared in Another Scope

A variable or function declared inside a PL/SQL block is only accessible within that block. If you try to access it from another scope, you’ll encounter this error.

Example

DECLARE
    v_count NUMBER := 10;
BEGIN
    DECLARE
        v_total NUMBER;
    BEGIN
        v_total := v_count;  -- Error: v_count is not visible in this scope
    END;
END;
/

Solution

Ensure that the variable is accessible in the correct scope or use an OUT parameter for procedures.

4. Lack of Necessary Privileges

If the referenced object exists but you lack the required privileges, Oracle will raise this error.

Example

SELECT first_name FROM hr.employees;

If the user does not have SELECT privileges on hr.employees, Oracle will throw PLS-00201.

Solution

Grant the necessary privileges:

GRANT SELECT ON hr.employees TO current_user;

5. Calling Private Package Functions or Procedures

If a procedure or function inside a package is declared as PRIVATE, it cannot be accessed outside the package.

Example

BEGIN
    my_package.private_function;
END;
/

Solution

Make the function public in the package specification:

CREATE OR REPLACE PACKAGE my_package AS
    FUNCTION public_function RETURN VARCHAR2;
END my_package;
/

6. The Object is Invalid or Does Not Exist

If an object is invalid (e.g., a dropped table or an invalid procedure), calling it will result in this error.

Example

BEGIN
    invalid_procedure;
END;
/

Solution

Recompile the invalid object or recreate it if it was dropped:

ALTER PROCEDURE my_procedure COMPILE;

How to Troubleshoot PLS-00201 Errors

To diagnose and fix this error, follow these steps:

1. Check Spelling: Ensure the identifier is spelled correctly.
2. Verify Declaration: Make sure the variable, function, or procedure is properly declared.
3. Confirm Scope: Ensure that the identifier is accessible within the correct scope.
4. Check Privileges: Use the following query to check if you have the required permissions:SELECT * FROM user_tab_privs WHERE table_name = 'EMPLOYEES';
5. Recompile Invalid Objects:SELECT object_name, status FROM user_objects WHERE status = 'INVALID';Then, recompile:ALTER PACKAGE my_package COMPILE;
6. Use Fully Qualified Names: If referencing another schema, prefix the object with the schema name:SELECT first_name FROM hr.employees;

Conclusion

The PLS-00201: Identifier must be declared error is a common issue in PL/SQL programming, typically caused by undeclared variables, misspelled object names, scope issues, or insufficient privileges. By following best practices such as declaring identifiers properly, using correct object references, and granting necessary privileges, you can easily avoid or fix this error.

By carefully debugging your PL/SQL code, checking for missing declarations, and verifying user permissions, you can ensure that your Oracle database programs run smoothly and efficiently.