📋

Description

The PLS-00201 error in Oracle Database indicates an attempt to reference an undeclared identifier, such as a variable, procedure, or exception. This error can also occur if you lack the necessary privileges or if privileges were granted through a role that is not enabled in the current context.

💬

Error Message

identifier ' string ' must be declared

🔍

Known Causes

4 known causes

⚠️

Undeclared Identifier

The referenced variable, procedure, exception, or other item has not been declared within the current scope.

⚠️

Insufficient Privileges

You lack the necessary privileges to access the referenced item; you may need to be granted access by the owner.

⚠️

Role-Based Privileges

Privileges were granted only through a role, which are not enabled for stored objects (packages, procedures, functions, triggers, views) during execution.

⚠️

Incorrect Scope

The identifier is declared, but it is not within the scope of the code where it is being referenced.

🛠️

Solutions

4 solutions available

1. Verify Object Existence and Schema Ownership easy

Ensure the identifier (table, view, procedure, etc.) exists and is accessible by the current schema.

1

Connect to the Oracle database as the user or schema that is encountering the PLS-00201 error.

2

Query the `ALL_OBJECTS` or `USER_OBJECTS` data dictionary view to confirm the existence of the identifier. Replace 'YOUR_IDENTIFIER_NAME' with the actual name causing the error.

SELECT object_name, owner, object_type
FROM all_objects
WHERE object_name = 'YOUR_IDENTIFIER_NAME' AND owner = 'SCHEMA_OWNER';

-- Or if the object should be owned by the current user:
SELECT object_name, object_type
FROM user_objects
WHERE object_name = 'YOUR_IDENTIFIER_NAME';

3

If the object does not exist, create it. If it exists but is owned by a different schema, you will need to qualify the object name with the owner's schema name (e.g., `SCHEMA_OWNER.YOUR_IDENTIFIER_NAME`).

2. Grant Necessary Privileges easy

The user executing the PL/SQL code may lack the required privileges on the referenced object.

1

Identify the user or role that is executing the PL/SQL code that is failing.

2

Connect to the database as a user with sufficient privileges (e.g., `SYS` or the object owner) to grant roles and privileges.

3

Grant the appropriate privileges (e.g., `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `EXECUTE`) on the object to the user or role. Replace 'OBJECT_OWNER', 'OBJECT_NAME', 'USER_OR_ROLE', and 'PRIVILEGE' with the correct values.

GRANT privilege ON OBJECT_OWNER.OBJECT_NAME TO USER_OR_ROLE;

4

If the object is a package, procedure, or function, grant `EXECUTE` privilege.

GRANT EXECUTE ON OBJECT_OWNER.PACKAGE_NAME TO USER_OR_ROLE;

3. Check for Typos and Case Sensitivity easy

Simple typographical errors or incorrect case in the identifier name are common causes.

1

Carefully review the PL/SQL code where the error occurs. Pay close attention to the exact spelling and capitalization of the identifier that is reported as undeclared.

2

Compare the identifier in the code with the actual name of the object in the database. Oracle object names are case-sensitive if they were created using double quotes (e.g., `"MyTable"`). If not quoted, they are typically stored in uppercase.

3

Correct any typos or case mismatches in your PL/SQL code.

4. Examine Package/Procedure Dependencies medium

If the undeclared identifier is within a package or procedure, it might be a dependency issue or a missing declaration within the package spec.

1

If the error occurs within a package specification or body, check if the identifier is declared within that package. If it's a variable, constant, or type, it should be declared in the `DECLARE` section or as a package-level member.

PACKAGE my_package IS
  -- If 'MY_VARIABLE' is causing PLS-00201, it needs to be declared here or in the body.
  -- MY_VARIABLE VARCHAR2(50);
END my_package;

2

If the identifier is a procedure or function within the same package, ensure it is declared in the package specification if it needs to be externally accessible.

PACKAGE my_package IS
  PROCEDURE my_procedure;
END my_package;

PACKAGE BODY my_package IS
  PROCEDURE my_procedure IS
  BEGIN
    -- Code here
  END my_procedure;
END my_package;

3

If the identifier is an object from another schema or package, ensure that the current schema has execute privileges on that package or direct access to the object.

4

Recompile the package or procedure that is encountering the error. This can sometimes resolve dependency issues.

ALTER PACKAGE package_name COMPILE;
ALTER PROCEDURE procedure_name COMPILE;

Oracle Error PLS-00201