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:
- "FOR LOOP Statement Overview" for more information about the concepts and examples
- "FOR LOOP Statement" for more information about the syntax and semantics
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.
See Also:
- "Qualified Expressions Overview" for more information and examples
- "Qualified Expression" for more information about the syntax and semantics
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:
- "CREATE FUNCTION Statement" for more information about the syntax and semantics
- "SQL_MACRO Clause" syntax and semantics
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.
See Also:
-
"SQL Functions in PL/SQL Expressions" for more information
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:
- "Pragmas" for more information about pragmas
- "SUPPRESSES_WARNING_6009 Pragma" for more information about the syntax and semantics
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.
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:
- Oracle Database Upgrade Guide for more information about desupported features in this release of Oracle Database