My Oracle Support Banner

Accessing Table/View from PL/SQL Raises ORA-942 After Container Is Switched by the Same PL/SQL Code (Doc ID 2775107.1)

Last updated on MAY 12, 2021

Applies to:

PL/SQL - Version 12.1.0.1 and later
Information in this document applies to any platform.

Symptoms

The "V$" views or any other table/view owned by a custom schema cannot be referenced inside PL/SQL code when the PDB is changed by executing 'alter session set container' command from the same PL/SQL code. However, the same table or view can be accessed from PL/SQL when the PDB is changed using a different PL/SQL program or the PDB is changed manually outside the PL/SQL code. The issue can be demonstrated using the following test case.

The issue is not only restricted to SYS owned dictionary tables/views, the "ORA-00942" can be seen with any table/view owned by the schema('C##TEST') itself or any other custom schema.

Changes

It's newly installed or upgraded multi-tenant database.

Cause

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
Symptoms
Changes
Cause
Solution
References


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