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:
More PL/SQL-Only Data Types Can Cross PL/SQL-to-SQL Interface
Can Grant Roles to PL/SQL Packages and Standalone Subprograms
LIBRARY Can Be Defined as DIRECTORY Object and With CREDENTIAL
Predefined Inquiry Directives $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE
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:
The PL/SQL-only data type must be either predefined (like BOOLEAN in Example 7-2) or declared in a package specification (like the record in Example 7-3).
If the PL/SQL-only data type is an associative array, then it must be indexed by PLS_INTEGER, as in Example 7-4.
A PL/SQL function cannot return a value of a PL/SQL-only type to SQL.
A BOOLEAN literal (TRUE, FALSE, or 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 ACCESSIBLE 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:
The ACCESSIBLE BY clause supplements the standard Oracle security mechanisms. It cannot authorize an otherwise illegal reference.
The optional FETCH FIRST clause limits the number of rows that a query returns, significantly reducing the SQL complexity of common "Top-N" queries.
FETCH FIRST is provided primarily to simplify migration from third-party databases to Oracle Database. However, it can also improve the performance of some SELECT BULK COLLECT 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 VARCHAR2, NVARCHAR2, and RAW had different maximum sizes in SQL and PL/SQL. In SQL, the maximum size of VARCHAR2 and 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.
As of Oracle Database 12c, you can create a DATABASE event trigger on a pluggable database (PDB). For syntax and semantics, see "CREATE TRIGGER Statement". For general information about PDBs, see Oracle Database Administrator's Guide.
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.
Using 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 OUT REF 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 OUT REF 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 BEQUEATH DEFINER (the default), which behaves like a DR unit, or BEQUEATH 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 INHERIT PRIVILEGES privilege on the invoker or the INHERIT ANY 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 DESCRIBE command, SELECT * commands, Oracle Call Interface (OCI) describes, and PL/SQL %ROWTYPE attribute.
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".
See Also:
Oracle Database SQL Language Reference for more information about invisible columnsBefore 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 EDITIONABLE or NONEDITIONABLE.
See Also:
Oracle Database Development Guide for complete information about editioned and noneditioned objectsAs of Oracle Database 12c, two kinds of PL/SQL functions might run faster in SQL:
PL/SQL functions that are declared and defined in the WITH clauses of SQL SELECT statements, described in Oracle Database SQL Language Reference
PL/SQL functions that are defined with the "UDF Pragma"
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_OWNER and $$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 PLSQL_OPTIMIZE_LEVEL=1.
For information about compilation parameters, see "PL/SQL Units and Compilation Parameters",