These are the PL/SQL bugs in Oracle Database 18c.
220.127.116.11 Bug 5910872
In Oracle Database releases prior to 18c, the PL/SQL compiler collected metadata for a PL/SQL package type argument and all of its nested types, and inserted that data into dictionary tables such that the data could be viewed using the
USER_ARGUMENTS user views. For instance, consider the following type declarations in the package
Type Level2Record is RECORD (Field1 NUMBER); Type Level1Collection is TABLE of Level2Record index by binary_integer; Type Level0Record is RECORD (Field1 Level1Collection); Procedure NestedTypesProc (Param1 Level0Record);
USER_ARGUMENTS user views are queried, the top-level type of the
Level0Record, is returned along with an expanded description of all the nested types within
SQL> select argument_name,type_subname,position,sequence,data_level from user_arguments where object_name='NESTEDTYPESPROC'; ARGUMENT_NAME TYPE_SUBNAME POSITION SEQUENCE DATA_LEVEL --------------- ----------------- ---------- -------- ---------- PARAM1 LEVEL0RECORD 1 1 0 FIELD1 LEVEL1COLLECTION 1 2 1 LEVEL2RECORD 1 3 2 FIELD1 1 4 3
This metadata is stored because the PL/SQL package type descriptive metadata was not user accessible in the way that metadata is accessible for top-level object types. With top-level object types and collections, you can query
ALL_TYPES and the associated user views,
ALL_COLL_TYPES, to obtain type metadata. However, before Oracle Database 12c Release 1 (12.1), type metadata for PL/SQL package types, such as records and packaged collections, could not be obtained. Therefore, function or procedure parameters that referenced those PL/SQL package types resulted in publishing all of the metadata about these types in the
ARGUMENTS views, including any nested types.
Deeply nested types can consume extensive memory in the
SYS tablespace. Also, because there is no way to share the type metadata in the
ARGUMENTS views, each parameter with deeply nested types requires its own redundant copy of the type metadata. Copious amounts of metadata in the
ARGUMENTS views and
SYS tablespace can lead to various issues, including PL/SQL compiler performance degradation. The degradation occurs because of the time it takes PL/SQL to update rows in the underlying dictionary tables.
In Oracle Database 12c Release 1 (12.1), PL/SQL introduced enhanced support for package types, including the new user views,
ALL_PLSQL_COLL_TYPES. As the names imply, these views allow users to query metadata about PL/SQL package types.
Because of the package type support that was added with Oracle Database 12c Release 1 (12.1), there was no longer a need for the PL/SQL compiler to insert large amounts of descriptive metadata into the
ARGUMENTS views. A single row of metadata that includes the type name was all that was required in the
ARGUMENTS views for each parameter type. You can obtain a full description of the type name, and any nested types, in a query against the PL/SQL type views.
Beginning with Oracle Database 18c, the
ARGUMENTS views contain fewer rows. In particular, only top-level (
DATA_LEVEL=0) items are stored in the
For instance, the query shown returns the following reduced number of rows (only those rows where
DATA_LEVEL=0) when run in Oracle Database 18c:
ARGUMENT_NAME TYPE_SUBNAME POSITION SEQUENCE DATA_LEVEL --------------- ----------------- ---------- -------- ---------- PARAM1 LEVEL0RECORD 1 1 0
OCIDescribeAny() is based on the same metadata used by the
OCIDescribeAny() also returns a single row for each parameter type, instead of the multiple rows commonly returned before the change in Oracle Database 12c Release 1 (12.1).
USER_ARGUMENTS user views also contain a new column type,
TYPE_OBJECT_TYPE. To determine the type of the type described by
TYPE_SUBNAME, use the
TYPE_OBJECT_TYPE column. The possible values include
If you require the previous behavior for collecting argument metadata, then you can set events to
events='10946, level 65536'. Setting this event reverts the
ARGUMENTS views to the behavior in Oracle Database releases earlier than Oracle Database 18c, in which
DATA_LEVEL can be greater than
0, and descriptive metadata for the type and any nested types are included in the view. If you make this change, then you must recompile the affected packages after you set the event. When you recompile the affected packages, the compiler recollects the additional metadata. This event also reverts
OCIDescribeAny() to the behavior in releases earlier than Oracle Database 18c.
Starting in Oracle Database 12c Release 1 (18.104.22.168), if you entered a procedure with no arguments, then the
ARGUMENTS views did not have any rows. This is an additional change separate from the row reduction change to the
ARGUMENTS views. Before Oracle Database 12c Release 1 (22.214.171.124), a procedure with no arguments was presented as a single row in the