This preface lists changes in Oracle Database PL/SQL Language Reference.
For Oracle Database 12c Release 1 (12.1), Oracle Database PL/SQL Language Reference documents these new features:
Before Oracle Database 12c, an invoker's rights function could not be result-cached.
As of Oracle Database 12c, this restriction is gone.
For information about invoker's rights functions, see "Invoker's Rights and Definer's Rights (AUTHID Property)". For information about result caching, see "PL/SQL Function Result Cache".
Before Oracle Database 12c, values with PL/SQL-only data types (for example,
BOOLEAN, associative array, and record) could not be bound from client programs (OCI or JDBC) or from static and native dynamic SQL issued from PL/SQL in the server.
As of Oracle Database 12c, it is possible to bind values with PL/SQL-only data types to anonymous blocks (which are SQL statements), PL/SQL function calls in SQL queries and
CALL statements, and the
TABLE operator in SQL queries. However:
If the PL/SQL-only data type is an associative array, then it must be indexed by
PLS_INTEGER, as in Example 7-4.
If the PL/SQL-only data type is an associative array, it cannot be used within a non-query DML statement (INSERT, UPDATE, DELETE, MERGE) nor in a subquery.
A PL/SQL function cannot return a value of a PL/SQL-only type to SQL.
BOOLEAN literal (
NULL) cannot be an argument to a PL/SQL function that is called from a static SQL query or from a Java/JDBC application.
In SQL contexts, you cannot use a function whose return type was declared in a package specification.
You might implement a database application as several PL/SQL packages—one package that provides the application programming interface (API) and helper packages to do the work. Ideally, only the API is accessible to clients.
Also, you might create a utility package to provide services to only some other PL/SQL units in the same schema. Ideally, the utility package is accessible only to the intended PL/SQL units.
Before Oracle Database 12c, PL/SQL could not prevent clients from using items exposed in helper packages. To isolate these items, you had to use relational database management system (RDBMS) security features. Some application deployment schemes made RDBMS security features hard to use.
As of Oracle Database 12c, each of these statements has an optional
BY clause that lets you specify a white list of PL/SQL units that can access the PL/SQL unit that you are creating or altering:
BY clause supplements the standard Oracle security mechanisms. It cannot authorize an otherwise illegal reference.
FIRST clause limits the number of rows that a query returns, significantly reducing the SQL complexity of common "Top-N" queries.
FIRST is provided primarily to simplify migration from third-party databases to Oracle Database. However, it can also improve the performance of some
INTO statements. For more information, see "Row Limits for SELECT BULK COLLECT INTO Statements".
Before Oracle Database 12c, a definer's rights (DR) unit always ran with the privileges of the definer and an invoker's rights (IR) unit always ran with the privileges of the invoker. If you wanted to create a PL/SQL unit that all users could invoke, even if their privileges were lower than yours, then it had to be a DR unit. The DR unit always ran with all your privileges, regardless of which user invoked it.
As of Oracle Database 12c, you can grant roles to individual PL/SQL packages and standalone subprograms. Instead of a DR unit, you can create an IR unit and then grant it roles. The IR unit runs with the privileges of both the invoker and the roles, but without any additional privileges that you have.
For more information, see "Granting Roles to PL/SQL Packages and Standalone Subprograms".
Before Oracle Database 12c, the data types
RAW had different maximum sizes in SQL and PL/SQL. In SQL, the maximum size of
NVARCHAR2 was 4,000 bytes and the maximum size of
RAW was 2,000 bytes. In PL/SQL, the maximum size of each of these data types was 32,767 bytes.
As of Oracle Database 12c, the maximum size of each of these data types is 32,767 bytes in both SQL and PL/SQL. However, SQL has these maximum sizes only if the
MAX_STRING_SIZE initialization parameter is set to
EXTENDED. For information about extended data types, see Oracle Database SQL Language Reference.
Before Oracle Database 12c:
You could define a
LIBRARY object only by using an explicit path, even in versions of Oracle Database where the
DIRECTORY object was intended as the single point of maintenance for file system paths.
When running a subprogram stored in a library, the
extproc agent always impersonated the owner of the Oracle Database installation.
As of Oracle Database 12c:
You can define a
LIBRARY object by using either an explicit path or a
DIRECTORY object improves the security and portability of an application that uses external procedures.
When you define a
LIBRARY object, you can use the
CREDENTIAL clause to specify the operating system user that the
extproc agent impersonates when running a subprogram stored in the library. (The default is the owner of the Oracle Database installation.)
For more information, see "CREATE LIBRARY Statement".
Before Oracle Database 12c, a PL/SQL stored subprogram returned result sets from SQL queries explicitly, through
CURSOR parameters, and the client program that invoked the subprogram had to bind to those parameters explicitly to receive the result sets.
As of Oracle Database 12c, a PL/SQL stored subprogram can return query results to its client implicitly, using the PL/SQL package
DBMS_SQL instead of
CURSOR parameters. This technique makes it easy to migrate applications that rely on the implicit return of query results from stored subprograms from third-party databases to Oracle Database. For more information, see "DBMS_SQL.RETURN_RESULT Procedure" and "DBMS_SQL.GET_NEXT_RESULT Procedure".
Before Oracle Database 12c, a view always behaved like a definer's rights (DR) unit.
As of Oracle Database 12c, a view can be either
DEFINER (the default), which behaves like a DR unit, or
CURRENT_USER, which behaves somewhat like an invoker's rights (IR) unit—for details, see Oracle Database Security Guide. For general information about DR and IR units, see "Invoker's Rights and Definer's Rights (AUTHID Property)".
Before Oracle Database 12c, an IR unit always ran with the privileges of its invoker. If its invoker had higher privileges than its owner, then the IR unit might perform operations unintended by, or forbidden to, its owner.
As of Oracle Database 12c, an IR unit can run with the privileges of its invoker only if its owner has either the
PRIVILEGES privilege on the invoker or the
PRIVILEGES privilege. For more information, see "Invoker's Rights and Definer's Rights (AUTHID Property)".
An invisible column is a user-specified hidden column that differs from a system-generated hidden column in these ways:
You can explicitly specify the name of an invisible column wherever you can explicitly specify the name of a visible column.
To display or assign a value to an invisible column, you must specify its name explicitly—not implicitly, as in the SQL*Plus
SELECT * commands, Oracle Call Interface (OCI) describes, and PL/SQL
You can make an invisible column visible.
Making an invisible column visible changes the structure of some records defined with the
%ROWTYPE attribute. For details, see "%ROWTYPE Attribute and Invisible Columns".
Oracle Database SQL Language Reference for more information about invisible columns
Before Oracle Database 12c, a schema object was editionable if its type was editionable in the database and its owner was editions-enabled. An editions-enabled user could not own a noneditioned object of an editionable type.
As of Oracle Database 12c, a schema object is editionable if its type is editionable in the schema that owns it and it has the
EDITIONABLE property. An editions-enabled user can own a noneditioned object of a type that is editionable in the database if the type is noneditionable in the schema or the object has the
NONEDITIONABLE property. Therefore, the "CREATE [ OR REPLACE ] Statements" and "ALTER Statements" let you specify
Oracle Database Development Guide for complete information about editioned and noneditioned objects
Before Oracle Database 12c, diagnostic code could identify only the name of the current PL/SQL unit (with the predefined inquiry directive
$$PLSQL_UNIT) and the number of the source line on which the predefined inquiry directive
$$PLSQL_LINE appeared in that unit.
As of Oracle Database 12c, the additional predefined inquiry directives
$$PLSQL_UNIT_TYPE let diagnostic code identify the owner and type of the current PL/SQL unit. For more information, see "Predefined Inquiry Directives".
The compilation parameter
PLSQL_DEBUG, which specifies whether to compile PL/SQL units for debugging, is deprecated. To compile PL/SQL units for debugging, specify
For information about compilation parameters, see "PL/SQL Units and Compilation Parameters",