1. Database PL/SQL Language Reference
  2. Changes in Oracle Database PL/SQL Language Reference 12c Release 2 (12.2)

Changes in Oracle Database PL/SQL Language Reference 12c Release 2 (12.2)

For Oracle Database 12c release 2 (12.2), Oracle Database PL/SQL Language Reference documents these new features.

New Features

ACCESSIBLE BY Clause Enhancements

The ACCESSIBLE BY clause specifies a list of PL/SQL units that are considered safe to invoke the subprogram, and blocks all others.

Starting with Oracle Database 12c release 2 (12.2), the accessor list can be defined on individual subprograms in a package. This list is checked in addition to the accessor list defined on the package itself (if any). This list may only restrict access to the subprogram – it cannot expand access. This code management feature is useful to prevent inadvertent use of internal subprograms. For example, it may not be convenient or feasible to reorganize a package into two packages: one for a small number of procedures requiring restricted access, and another one for the remaining units requiring public access.

See Also:

ACCESSIBLE BY Clause for more information about the syntax and semantics.

Data-Bound Collation

Collation (also called sort ordering) is a set of rules that determines if a character string equals, precedes, or follows another string when the two strings are compared and sorted.

Different collations correspond to rules of different spoken languages. Collation-sensitive operations are operations that compare text and need a collation to control the comparison rules. The equality operator and the built-in function INSTR are examples of collation-sensitive operations.

Oracle Database 12c release 2 (12.2) adds a new architecture for controlling collation to be applied to operations on character data. In the new architecture, collation becomes an attribute of character data, analogous to a data type. You can now declare collation for a column and this collation is automatically applied by all collation-sensitive SQL operations referencing the column. The data-bound collation feature uses syntax and semantics compatible with the ISO/IEC SQL standard.

In this release, the PL/SQL language has limited support for the data-bound collation architecture. All data processed in PL/SQL expressions is assumed to have the compatibility collation USING_NLS_COMP. This pseudo-collation instructs collation-sensitive operators to behave in the same way as in previous Oracle Database releases. That is, the values of the session parameters NLS_COMP and NLS_SORT determine the collation to use. However, all SQL statements embedded or constructed dynamically in PL/SQL fully support the new architecture.

Oracle Database 12c release 2 adds a new property called default collation to tables, views, materialized views, packages, stored procedures, stored functions, triggers, and types. The default collation of a unit determines the collation for data containers, such as columns, variables, parameters, literals, and return values, that do not have their own explicit collation declaration in that unit. In this release, the default collation for packages, stored procedures, stored functions, triggers, and types must be USING_NLS_COMP.

For syntax and semantics, see the DEFAULT COLLATION Clause.

To facilitate the creation of PL/SQL units in a schema that has a schema default collation other than USING_NLS_COMP, the syntax and semantics for the following statements has changed to enable an explicit declaration of the object's default collation to be USING_NLS_COMP:
The compilation semantics of the COMPILE REUSE SETTINGS clause for the following statements has been amended:

See Also:

Controlling Definer’s Rights Privileges for Remote Procedures

If your applications use database links and definer’s rights procedures, then you can control how privileges are granted when users run the definer’s rights procedure.

A new privilege INHERIT REMOTE PRIVILEGES allows a current user to use a connected user database link from within a definer's rights (DR) procedure. Without this privilege, the DR procedure will not be able to connect using the connected user database link.

For more information, see Connected User Database Links in DR Units

PL/SQL Expressions Enhancements

Starting with Oracle Database 12c release 2 (12.2), expressions may be used in declarations where previously only literal constants were allowed.

Static expressions can now be used in subtype declarations.

The definition of static expressions is expanded to include all the PL/SQL scalar types and a much wider range of operators. Character operands are restricted to a safe subset of the ASCII character set. Operators whose results depend on any implicit NLS parameter are disallowed.

Expanded and generalized expressions have two primary benefits for PL/SQL developers:

  • Programs are much more adaptable to changes in their environment

  • Programs are more compact, clearer, and substantially easier to understand and maintain

See Static Expressions for more information.

Support for SQL JSON operators in PL/SQL

This feature makes it easier to work with JSON documents stored in an Oracle Database and to generate JSON documents from relational data.

Oracle Database support for storing and querying JSON documents in the database is extended by the addition of new capabilities, including the ability to declaratively generate JSON documents from relational data using SQL and the ability to manipulate JSON documents as PL/SQL objects. SQL JSON operators are supported in PL/SQL with a few exceptions. See SQL Functions in PL/SQL Expressions for the list of exceptions.

See Also:

Oracle Database JSON Developer's Guide for more information about how to use PL/SQL with JSON data stored in the database

Support for Longer Identifiers

The maximum length of all identifiers used and defined by PL/SQL is increased to 128 bytes, up from 30 bytes in previous releases.

If the COMPATIBLE parameter is set to a value of 12.2.0 or higher, the representation of the identifier in the database character set cannot exceed 128 bytes. If the COMPATIBLE parameter is set to a value of 12.1.0 or lower, the limit is 30 bytes.

A new function ORA_MAX_NAME_LEN_SUPPORTED has been introduced to check this limit. 

EXEC DBMS_OUTPUT.PUT_LINE(ORA_MAX_NAME_LEN_SUPPORTED);
128

A new constant ORA_MAX_NAME_LEN defines the name length maximum. New subtypes DBMS_ID and DBMS_QUOTED_ID define the length of identifiers in objects for SQL, PL/SQL and users.

See Also:

PL/SQL Coverage Pragma

The COVERAGE pragma marks PL/SQL code which is infeasible to test for coverage.

The mark improves the accuracy of the coverage metric analysis. For syntax and semantics, see the COVERAGE Pragma.

See Also:

PL/SQL Deprecation Pragma

The DEPRECATE pragma marks a PLSQL program element as deprecated.

The compiler warnings tell users of a deprecated element that other code may need to be changed to account for the deprecation.

For syntax and semantics, see the DEPRECATE Pragma.

Sharing Metadata-Linked Application Common Objects

A metadata link enables database objects in an application pluggable database (PDB) to share metadata with objects in the application root.

A new SHARING clause is introduced to specify how a stored PL/SQL unit can be shared between a PDB and an application root. Metadata links are useful for reducing disk and memory requirements because they store only one copy of an object’s metadata (such as the source code for a PL/SQL package) for identically defined objects. This improves performance of upgrade operations because changes to this metadata will be made in one place, the application root. See SHARING Clause for the syntax and semantics.

Support for Hybrid Columnar Compression (HCC) with Conventional DMLs

HCC can be used during array inserts with PL/SQL.

See Oracle Database Administrator's Guide for information about how to configure HCC

Deprecated Features

The following features are deprecated in this release, and may be desupported in a future release.

The command ALTER TYPE ... INVALIDATE is deprecated. Use the CASCADE clause instead.

The REPLACE clause of ALTER TYPE is deprecated. Use the alter_method_spec clause instead. Alternatively, you can recreate the type using the CREATE OR REPLACE TYPE statement.

For the syntax and semantics, see ALTER TYPE Statement

Desupported Features

Some features previously described in this document are desupported in Oracle Database 12c release 2 (12.2).

  • Desupport of server-side SQLJ

    Oracle supports using client-side SQLJ. However, Oracle does not support the use of server-side SQLJ, including running stored procedures, types, functions, and triggers in the database environment.

See Also: