1. Database PL/SQL Language Reference
  2. Changes in This Release for Oracle Database PL/SQL Language Reference

1 Changes in This Release for Oracle Database PL/SQL Language Reference

1.1 New Features in Release 21c for Oracle Database PL/SQL Language Reference

For Oracle Database Release 21c, PL/SQL Language Reference documents these new features and enhancements.

See Also:

Learning Key New Features for Database Administrators for the descriptions of all of the features that are new in Oracle Database Release 21c as well as details and practices

1.1.1 PL/SQL Extended Iterators

PL/SQL programs use iteration controls to implement business logic over rows of data generated by SQL queries.

Iteration is the basic building block of PL/SQL. PL/SQL is enhanced to help you program iteration controls using new iterators in loops and in qualified expressions. The iterators are clear, simple, understandable, and efficient.

These iteration controls available are:

  • Stepped range iteration controls
  • Single expression iteration controls
  • Collections iteration controls
  • Cursor iteration controls

Multiple iteration controls may be chained together.

New stopping and skipping predicate clauses have been added.

The new mutability property of an iterand determines whether or not it can be assigned to in the loop body.

An iterand type can be implicitly or explicitly declared.

See Also:

1.1.2 PL/SQL Qualified Expressions Enhancements

A qualified expression combines expression elements to create values of almost any type. Aggregates and their necessary adjunct, qualified expressions, improve program clarity and programmer productivity.

Starting with Oracle Database Release 18c, any PL/SQL value can be provided by an expression like a constructor provides an abstract datatype value. In PL/SQL, we use the terms "qualified expression" and "aggregate" rather than the SQL term "type constructor", but the functionality is the same. Qualified expressions are most useful for records, associative arrays, nested tables, and varrays.

Starting with Oracle Database Release 21c, new types of iterator choice association are added for use in qualified expressions. The basic iterator choice association extends the current iterator choice association by allowing a full iterator as the index. The index iterator choice association provides an index expression along with the value expression. The sequence iterator choice association allows a sequence of values to be added to the end of a collection. In each case, the expressions specified may reference the iterands. More complex selectors can be specified using alternation, iteration, and starting with Oracle Database release 21c, version 21.3, using the OTHERS selector.

See Also:

1.1.3 SQL Macros

You can create SQL macros (SQM) to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements.

SQL macros can either be scalar expressions, typically used in SELECT lists, WHERE, GROUP BY, and HAVING clauses, to encapsulate calculations and business logic, or can be table expressions, typically used in a FROM clause, to act as a sort of polymorphic (parameterized) views.

SQL macros increase developer productivity, simplify collaborative development, and improve code quality.

See Also:

1.1.4 New JSON Data Type

JSON is a new SQL and PL/SQL data type for JSON data. The data is stored in the database in a binary form for faster access to nested JSON values.

You can use JSON data type and its instances in most places where a SQL data type is allowed, including:
  • As the column type for table or view DDL
  • As a parameter type for a PL/SQL subprogram
  • In expressions wherever a SQL/JSON function or condition are allowed

Some restrictions apply.

See Also:

1.1.5 New Pragma SUPPRESSES_WARNING_6009

The SUPPRESSES_WARNING_6009 pragma allows more robust error handling and better encapsulation and modularization.

The PL/SQL compiler issues warning PLW-06009 if it determines that an OTHERS exception handler does not, in all cases, end in either an explicit RAISE statement or in a call to the PL/SQL supplied procedure RAISE_APPLICATION_ERROR. The compiler’s behavior may be too aggressive for some programming styles when programmers supply their own reporting subroutines. This new pragma allows to quiet the warning.

See Also:

1.1.6 PL/SQL Type Attributes in Non Persistable User Defined Types

You can use attributes of PL/SQL scalar data types, such as BOOLEAN and PLS_INTEGER, in non-persistable objects.

Instances of non-persistable types cannot persist on disk.

You can use non-persistable object types in your PL/SQL code if you have no desire to persist instances of these types. This is useful when you are developing programs following Oracle's object oriented programming model.

See Also:

  • CREATE TYPE statement [NOT] PERSISTABLE clause for more information about the syntax and semantics

1.1.7 PL/SQL Function Enhanced Result Cache

The result cache enhancements improve database performance, broadens its use cases, and reduces the overall workload.

Result cache functionality is enhanced to provide better scalability, provide better control of what is being cached, and to broaden the applicability to cache results beyond the limits of pure-memory storage.

New functionality includes blocklisting of statements, PL/SQL function history tracking, object blocklisting, and allowing result caching to spill to disk. A function that is invoked frequently with different arguments may generate results that are rarely reused, leading to performance degradation. Oracle Database tracks recently used PL/SQL functions that have the RESULT_CACHE annotation. The database only caches a PL/SQL function and arguments pair if it has seen it in recent history. Using this history, the database only caches a PL/SQL function and arguments pair if it has seen it x times in recent history, where x is set by the initialization parameter RESULT_CACHE_EXECUTION_THRESHOLD. Before fetching a cached result from a remote instance, the database uses heuristics to determine if it is more cost efficient to recompute the result on the local instance

See Also:

"PL/SQL Function Result Cache" for more information

1.2 Deprecated Features

The following features are deprecated, 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

Starting with Oracle Database 12c release 1 (12.1), the compilation parameter PLSQL_DEBUG is deprecated.

To compile PL/SQL units for debugging, specify PLSQL_OPTIMIZE_LEVEL=1.

For information about compilation parameters, see PL/SQL Units and Compilation Parameters.

1.3 Desupported Features

No features in PL/SQL Language Reference have been desupported.

See Also: