1.1 New Features in Release 20c for Oracle Database PL/SQL Language Reference
For Oracle Database 20c, Oracle Database PL/SQL Language Reference documents these new features and enhancements.
Oracle Database New Features Guide for the descriptions of all of the features that are new in Oracle Database Release 20c
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
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 (for example for a record or for an associative array) 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.
Starting with Oracle Database Release 20c, three new types of iterator choice association are added for use in qualifed 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.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 for 20c.
- Oracle Database Upgrade Guide for more information about desupported features in Oracle Database 20c