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.
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.
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, three 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.
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
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.
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.
- 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.
"SQL Functions in PL/SQL Expressions" for more information
1.1.5 New Pragma SUPPRESSES_WARNING_6009
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.
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.
TYPEstatement [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
"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.
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.
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.
- Oracle Database Upgrade Guide for more information about desupported features in this release of Oracle Database