2.6.1 PL/SQL Known Bugs

These are the PL/SQL bugs in Oracle Database 18c.

• Bug 5910872
  

2.6.1.1 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 ALL, DBA, and USER_ARGUMENTS user views. For instance, consider the following type declarations in the package NestedTypesExample:

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);

When the ALL, DBA, and USER_ARGUMENTS user views are queried, the top-level type of the NestedTypeProc procedure, parameter Param1, Level0Record, is returned along with an expanded description of all the nested types within Level0Record.

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_TYPE_ATTRS and 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.

Workaround:

In Oracle Database 12c Release 1 (12.1), PL/SQL introduced enhanced support for package types, including the new user views, ALL_PLSQL_TYPES, ALL_PLSQL_TYPE_ATTRS, and 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 ARGUMENTS views.

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 ARGUMENTS views. 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).

ALL, DBA, and USER_ARGUMENTS user views also contain a new column type, TYPE_OBJECT_TYPE. To determine the type of the type described by TYPE_OWNER, TYPE_NAME and TYPE_SUBNAME, use the TYPE_OBJECT_TYPE column. The possible values include TABLE, VIEW, PACKAGE, and TYPE.

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 (12.1.0.2), 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 (12.1.0.2), a procedure with no arguments was presented as a single row in the ARGUMENTS views.