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

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.

See Also:

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.

See Also:

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.

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.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 for 20c.

See Also: