My Oracle Support Banner

An example to show one cause of a "PLS-00201: identifier must be declared" error (Doc ID 391047.1)

Last updated on APRIL 09, 2020

Applies to:

PL/SQL - Version 9.2.0.1 to 10.2.0.3 [Release 9.2 to 10.2]
Information in this document applies to any platform.
Information in this document applies to any platform.

Purpose

The "PLS-00201: identifier 'XYZ' must be declared" error is a fairly common yet misunderstood error due to how Oracle handles privileges granted directly and privileges granted via a Role in conjunction to Stored Procedures.

This Note should help illustrate the difference.

Scope

The example provided within creates two users where User1 owns the table and stored procedure and USER2 attempts to execute the procedure within User1's schema.

The stored procedure execute a SELECT from two tables.

User2 attempts to access User1's procedure which in turn Selects from User1's table.

Referencing of the procedure fails when the privileges are granted via a Role, but works when granted directly.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.