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 126.96.36.199 and later
Information in this document applies to any platform.
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.
It's newly installed or upgraded multi-tenant database.
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