For Oracle Database 12c release 2 (12.2), Oracle Database PL/SQL Language Reference documents these new features.
ACCESSIBLE BY Clause Enhancements
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.
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_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
For syntax and semantics, see the DEFAULT COLLATION Clause.
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
COMPILE REUSE SETTINGSclause for the following statements has been amended:
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
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.
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_QUOTED_ID define the length of identifiers in objects for SQL, PL/SQL and users.
PL/SQL Coverage Pragma
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.
PL/SQL Deprecation Pragma
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
The following features are deprecated in this release, and may be desupported in a future release.
ALTER TYPE ...
INVALIDATE is deprecated. Use the
CASCADE clause instead.
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.
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.
- Oracle Database Upgrade Guide for a list of all desupported features