My Oracle Support Banner

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 23, 2019

Applies to:

PL/SQL - Version 9.2.0.8 to 11.1.0.6 [Release 9.2 to 11.1]
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

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