An Example to Show One Cause of a "ORA-00942: table or view does not exist" Error Within a Stored Procedure
(Doc ID 391068.1)
Last updated on OCTOBER 09, 2023
Applies to:
PL/SQL - Version 9.2.0.8 to 11.1.0.6 [Release 9.2 to 11.1]Oracle Cloud Infrastructure - Database Service - Version N/A to N/A [Release 1.0]
Information in this document applies to any platform.
Information in this document applies to any platform.
Purpose
The "ORA-00942: table or view does not exist" error is a fairly common yet misunderstood error due to how Oracle handles privilege 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 two tables and a stored procedure and USER2 attempts to execute the procedure within User1's schema which in turn accesses the tables.
The stored procedure execute a SELECT from the two tables.
User2 attempts to access User1's procedure which in turn Selects from User1's table.
Execution of the procedure fails when the privileges to the tables 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 |
| References |