Introduction to PL/SQL New Features in Oracle 12c - Oracle PL/SQL by Example, Fifth Edition (2015)

Oracle PL/SQL by Example, Fifth Edition (2015)

Introduction to PL/SQL New Features in Oracle 12c

Oracle 12c has introduced a number of new features and improvements for PL/SQL. This introduction briefly describes features not covered in this book and points you to specific chapters for features that are within the scope of this book. The list of features described here is also available in the “Changes in This Release for Oracle Database PL/SQL Language Reference” section of the PL/SQL Language Reference manual offered as part of Oracle’s online help.

The new PL/SQL features and enhancements are as follows:

Image Invoker’s rights functions can be result-cached

Image More PL/SQL-only data types can cross the PL/SQL-to-SQL interface clause

Image ACCESSIBLE BY clause

Image FETCH FIRST clause

Image Roles can be granted to PL/SQL packages and stand-alone subprograms

Image More data types have the same maximum size in SQL and PL/SQL

Image Database triggers on pluggable databases

Image LIBRARY can be defined as DIRECTORY object and with CREDENTIAL clause

Image Implicit statement results

Image BEQUEATH CURRENT_USER views

Image INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES privileges

Image Invisible columns

Image Objects, not types, are editioned or noneditioned

Image PL/SQL functions that run faster in SQL

Image Predefined inquiry directives $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE

Image Compilation parameter PLSQL_DEBUG is deprecated

Invoker’s Rights Functions Can Be Result-Cached

When a stored subprogram is created in Oracle products, it may be created as either a definer rights (DR) unit or an invoker rights (IR) unit. A DR unit would execute with the permissions of its owner, whereas an IR unit would execute with the permissions of a user who invoked that particular unit. By default, a stored subprogram is created as a DR unit unless explicitly specified otherwise. Whether a particular unit is considered a DR or IR unit is controlled by the AUTHID property, which may be set to either DEFINER (default) or CURRENT_USER.

Prior to Oracle 12c, functions created with the invoker rights clause (AUTHID CURRENT_USER) could not be result-cached. To create a function as an IR unit, the AUTHID clause must be added to the function specification.

A result-cached function is a function whose parameter values and result are stored in the cache. As a consequence, when such a function is invoked with the same parameter values, its result is retrieved from the cache instead of being computed again. To enable a function for result-caching, the RESULT_CACHE clause must be added to the function specification. This is demonstrated by the following example (the invoker rights clause and result-caching are highlighted in bold).

For Example Result-Caching Functions Created with Invoker’s Rights

CREATE OR REPLACE FUNCTION get_student_rec (p_student_id IN NUMBER)
RETURN STUDENT%ROWTYPE
AUTHID CURRENT_USER
RESULT_CACHE RELIES_ON (student)
IS
v_student_rec STUDENT%ROWTYPE;
BEGIN
SELECT *
INTO v_student_rec
FROM student
WHERE student_id = p_student_id;

RETURN v_student_rec;
EXCEPTION
WHEN no_data_found
THEN
RETURN NULL;
END get_student_rec;
/

-- Execute newly created function
DECLARE
v_student_rec STUDENT%ROWTYPE;
BEGIN
v_student_rec := get_student_rec (p_student_id => 230);
END;

Note that if the student record for student ID 230 is in the result cache already, then the function will return the student record from the result cache. In the opposite case, the student record will be selected from the STUDENT table and added to the cache for future use. Because the result cache of the function relies on the STUDENT table, any changes applied and committed on the STUDENT table will invalidate all cached results for the get_student_rec function.

More PL/SQL-Only Data Types Can Cross the PL/SQL-to-SQL Interface Clause

In this release, Oracle has extended support of PL/SQL-only data types to dynamic SQL and client programs (OCI or JDBC). For example, you can bind collections variables when using the EXECUTE IMMEDIATE statement or the OPEN FOR, FETCH, and CLOSE statements. This topic is covered in greater detail in Lab 18.3, Binding Collections in SQL Statements, in Chapter 18.

ACCESSIBLE BY Clause

An optional ACCESSIBLE BY clause enables you to specify a list of PL/SQL units that may access the PL/SQL unit being created or modified. The ACCESSIBLE BY clause is typically added to the module header—for example, to the function or procedure header. Each unit listed in theACCESSIBLE BY clause is called an accessor, and the clause itself is also called a white list. This is demonstrated in the following example (the ACCESSIBLE BY clause is shown in bold).

For Example Procedure Created with the ACCESSIBLE BY Clause

CREATE OR REPLACE PROCEDURE test_proc1
ACCESSIBLE BY (TEST_PROC2)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE ('TEST_PROC1');
END test_proc1;
/

CREATE OR REPLACE PROCEDURE test_proc2
AS
BEGIN
DBMS_OUTPUT.PUT_LINE ('TEST_PROC2');
test_proc1;
END test_proc2;
/
-- Execute TEST_PROC2
BEGIN
test_proc2;
END;
/

TEST_PROC2
TEST_PROC1

-- Execute TEST_PROC1 directly
BEGIN
test_proc1;
END;
/

ORA-06550: line 2, column 4:
PLS-00904: insufficient privilege to access object TEST_PROC1
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

In this example, there are two procedures, test_proc1 and test_proc2, and test_proc1 is created with the ACCESSIBLE BY clause. As a consequence, test_proc1 may be accessed by test_proc2 only. This is demonstrated by two anonymous PL/SQL blocks. The first block executes test_proc2 successfully. The second block attempts to execute test_proc1 directly and, as a result, causes an error.

Note that both procedures were created within a single schema (STUDENT), and that both PL/SQL blocks were executed in the single session by the schema owner (STUDENT).

FETCH FIRST Clause

The FETCH FIRST clause is a new optional feature that is typically used with the “Top-N” queries as illustrated by the following example. The ENROLLMENT table used in this example contains student registration data. Each student is identified by a unique student ID and may be registered for multiple courses. The FETCH FIRST clause is shown in bold.

For Example Using FETCH FIRST Clause with “Top-N” Query

-- Sample student IDs from the ENROLLMENT table
SELECT student_id
FROM enrollment;

STUDENT_ID
----------
102
102
103
104
105
106
106
107
108
109
109
110
110
...

-- "Top-N" query returns student IDs for the 5 students that registered for the most
-- courses
SELECT student_id, COUNT(*) courses
FROM enrollment
GROUP BY student_id
ORDER BY courses desc
FETCH FIRST 5 ROWS ONLY;

STUDENT_ID COURSES
---------- -------
214 4
124 4
232 3
215 3
184 3

Note that FETCH FIRST clause may also be used in conjunction with the BULK COLLECT INTO clause as demonstrated here. The FETCH FIRST clause is shown in bold.

For Example Using FETCH FIRST Clause with BULK COLLECT INTO Clause

DECLARE
TYPE student_name_tab IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;

student_names student_name_tab;
BEGIN
-- Fetching first 20 student names only
SELECT first_name||' '||last_name
BULK COLLECT INTO student_names
FROM student
FETCH FIRST 20 ROWS ONLY;

DBMS_OUTPUT.PUT_LINE ('There are '||student_names.COUNT||' students');
END;
/
There are 20 students

Roles Can Be Granted to PL/SQL Packages and Stand-Alone Subprograms

Starting with Oracle 12c, you are able to grant roles to PL/SQL packages and stand-alone subprograms. Note that granting a role to a PL/SQL package or stand-alone subprogram does not alter its compilation. Instead, it affects how privileges required by the SQL statements that are issued by the PL/SQL unit at run time are checked.

Consider the following example where the READ role is granted to the function get_student_name.

For Example Granting READ Role to the get_student_name Function

GRANT READ TO FUNCTION get_student_name;

More Data Types Have the Same Maximum Size in SQL and PL/SQL

Prior to Oracle 12c, some data types had different maximum sizes in SQL and in PL/SQL. For example, in SQL the maximum size of NVARCHAR2 was 4000 bytes, whereas in PL/SQL it was 32,767 bytes. Starting with Oracle 12c, the maximum sizes of the VARCHAR2, NVARCHAR2, andRAW data types have been extended to 32,767 for both SQL and PL/SQL. To see these maximum sizes in SQL, the initialization parameter MAX_STRING_SIZE must be set to EXTENDED.

Database Triggers on Pluggable Databases

The pluggable database (PDB) is one of the components of Oracle’s multitenant architecture. Typically it is a portable collection of schemas and other database objects. Starting with Oracle 12c, you are able to create event triggers on PDBs. Detailed information on triggers is provided inChapters 13 and 14. Note that PDBs are outside the scope of this book, but detailed information on them may be found in Oracle’s online Administration Guide.

LIBRARY Can Be Defined as a DIRECTORY Object and with a CREDENTIAL Clause

A LIBRARY is a schema object associated with a shared library of an operating system. It is created with the help of the CREATE OR REPLACE LIBRARY statement. A DIRECTORY is also an object that maps an alias to an actual directory on the server file system. The DIRECTORYobject is covered very briefly in Chapter 25 as part of the install processes for the PL/SQL Profiler API and PL/SQL Hierarchical Profiler. In the Oracle 12c release, a LIBRARY object may be defined as a DIRECTORY object with an optional CREDENTIAL clause as shown here.

For Example Creating LIBRARY as DIRECTORY Object

CREATE OR REPLACE LIBRARY my_lib AS 'plsql_code' IN my_dir;

In this example, the LIBRARY object my_lib is created as a DIRECTORY object. The 'plsql_code' is the name of the dynamic link library (DDL) in the DIRECTORY object my_dir. Note that for this library to be created successfully, the DIRECTORY object my_dir must be created beforehand. More information on LIBRARY and DIRECTORY objects can be found in Oracle’s online Database PL/SQL Language Reference.

Implicit Statement Results

Prior to Oracle release 12c, result sets of SQL queries were returned explicitly from the stored PL/SQL subprograms via REF CURSOR out parameters. As a result, the invoker program had to bind to the REF CURSOR parameters and fetch the result sets explicitly as well.

Starting with this release, the REF CURSOR out parameters can be replaced by two procedures of the DBMS_SQL package, RETURN_RESULT and GET_NEXT RESULT. These procedures enable stored PL/SQL subprograms to return result sets of SQL queries implicitly, as illustrated in the following example (the reference to the RETURN_RESULT procedure is highlighted in bold):

For Example Using DBMS_SQL.RETURN_RESULT Procedure

CREATE OR REPLACE PROCEDURE test_return_result
AS
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur
FOR
SELECT first_name, last_name
FROM instructor
FETCH FIRST ROW ONLY;

DBMS_SQL.RETURN_RESULT (v_cur);
END test_return_result;
/

BEGIN
test_return_result;
END;
/

In this example, the test_return_result procedure returns the instructor’s first and last names to the client application implicitly. Note that the cursor SELECT statement employs a FETCH FIRST ROW ONLY clause, which was introduced in Oracle 12c as well. To get the result set from the procedure test_return_result successfully, the client application must likewise be upgraded to Oracle 12c. Otherwise, the following error message is returned:

ORA-29481: Implicit results cannot be returned to client.
ORA-06512: at "SYS.DBMS_SQL", line 2785
ORA-06512: at "SYS.DBMS_SQL", line 2779
ORA-06512: at "STUDENT.TEST_RETURN_RESULT", line 10
ORA-06512: at line 2

BEQUEATH CURRENT_USER Views

Prior to Oracle 12c, a view could be created only as a definer rights unit. Starting with release 12c, a view may be created as an invoker’s rights unit as well (this is similar to the AUTHID property of a stored subprogram). For views, however, this behavior is achieved by specifying aBEQUEATH DEFINER (default) or BEQUEATH CURRENT_USER clause at the time of its creation as illustrated by the following example (the BEQUEATH CURRENT_USER clause is shown in bold):

For Example Creating View with BEQUEATH CURRENT_USER Clause

CREATE OR REPLACE VIEW my_view
BEQUEATH CURRENT_USER
AS
SELECT table_name, status, partitioned
FROM user_tables;

In this example, my_view is created as an IR unit. Note that adding this property to the view does not affect its primary usage. Rather, similarly to the AUTHID property, it determines which set of permissions will be applied at the time when the data is selected from this view.

INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges

Starting with Oracle 12c, an invoker’s rights unit will execute with the invoker’s permissions only if the owner of the unit has INHERIT PRIVILEGES or INHERIT ANY PRIVILEGES privileges. For example, before Oracle 12c, suppose user1 created a function F1 as an invoker’s rights unit and granted execute privilege on it to user2, who happened to have more privileges than user1. Then when user2 ran function F1, the function would run with the permissions of user2, potentially performing operations for which user1 might not have had permissions. This is no longer the case with Oracle 12c. As stated previously, such behavior must be explicitly specified via INHERIT PRIVILEGES or INHERIT ANY PRIVILEGES privileges.

Invisible Columns

Starting with Oracle 12c, it is possible to define and manipulate invisible columns. In PL/SQL, records defined as %ROWTYPE are aware of such columns, as illustrated by the following example (references to the invisible columns are shown in bold):

For Example %ROWTYPE Records and Invisible Columns

-- Make NUMERIC_GRADE column invisible
ALTER TABLE grade MODIFY (numeric_grade INVISIBLE);
/
table GRADE altered

DECLARE
v_grade_rec grade%ROWTYPE;
BEGIN
SELECT *
INTO v_grade_rec
FROM grade
FETCH FIRST ROW ONLY;

DBMS_OUTPUT.PUT_LINE ('student ID: '||v_grade_rec.student_id);
DBMS_OUTPUT.PUT_LINE ('section ID: '||v_grade_rec.section_id);
-- Referencing invisible column causes an error
DBMS_OUTPUT.PUT_LINE ('grade: '||v_grade_rec.numeric_grade);
END;
/
ORA-06550: line 12, column 54:
PLS-00302: component 'NUMERIC_GRADE' must be declared
ORA-06550: line 12, column 4:
PL/SQL: Statement ignored

-- Make NUMERIC_GRADE column visible
ALTER TABLE grade MODIFY (numeric_grade VISIBLE);
/
table GRADE altered

DECLARE
v_grade_rec grade%ROWTYPE;
BEGIN
SELECT *
INTO v_grade_rec
FROM grade
FETCH FIRST ROW ONLY;

DBMS_OUTPUT.PUT_LINE ('student ID: '||v_grade_rec.student_id);
DBMS_OUTPUT.PUT_LINE ('section ID: '||v_grade_rec.section_id);
-- This time the script executes successfully
DBMS_OUTPUT.PUT_LINE ('grade: '||v_grade_rec.numeric_grade);
END;
/
student ID: 123
section ID: 87
grade: 99

As you can gather from this example, the first run of the anonymous PL/SQL block did not complete due to the reference to the invisible column. Once the NUMERIC_GRADE column has been set to visible again, the script is able to complete successfully.

Objects, Not Types, Are Editioned or Noneditioned

An edition is a component of the edition-based redefinition feature that allows you to make a copy of an object—for example, a PL/SQL package—and make changes to it without affecting or invalidating other objects that may be dependent on it. With introduction of this feature, objects created in the database may be defined as editioned or noneditioned. For an object to be editioned, its object type must be editionable and it must have the EDITIONABLE property. Similarly, for an object to be noneditioned, its object type must be noneditioned or it must have theNONEDITIONABLE property.

Starting with Oracle 12c, you are able to specify whether a schema object is editionable or noneditionable in the CREATE OR REPLACE and ALTER statements. In this new release, a user (schema) that has been enabled for editions is able to own a noneditioned object even if its type is editionable in the database but noneditionable in the schema itself or if this object has NONEDITIONABLE property.

PL/SQL Functions That Run Faster in SQL

Starting with Oracle 12c, you can create user-defined functions that may run faster when they are invoked in the SQL statements. This may be accomplished as follows:

Image User-defined function declared in the WITH clause of a SELECT statement

Image User-defined function created with the UDF pragma

Consider the following example, where the format_name function is created in the WITH clause of the SELECT statement. This newly created function returns the formatted student name.

For Example Creating a User-Defined Function in the WITH Clause

WITH
FUNCTION format_name (p_salutation IN VARCHAR2
,p_first_name IN VARCHAR2
,p_last_name IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF p_salutation IS NULL
THEN
RETURN p_first_name||' '||p_last_name;
ELSE
RETURN p_salutation||' '||p_first_name||' '||p_last_name;
END IF;
END;
SELECT format_name (salutation, first_name, last_name) student_name
FROM student
FETCH FIRST 10 ROWS ONLY;

STUDENT_NAME
-----------------
Mr. George Kocka
Ms. Janet Jung
Ms. Kathleen Mulroy
Mr. Joel Brendler
Mr. Michael Carcia
Mr. Gerry Tripp
Mr. Rommel Frost
Mr. Roger Snow
Ms. Z.A. Scrittorale
Mr. Joseph Yourish

Next, consider another example where the format_name function is created with the UDF pragma.

For Example Creating a User-Defined Function in the UDF Pragma

CREATE OR REPLACE FUNCTION format_name (p_salutation IN VARCHAR2
,p_first_name IN VARCHAR2
,p_last_name IN VARCHAR2)
RETURN VARCHAR2
AS
PRAGMA UDF;
BEGIN
IF p_salutation IS NULL
THEN
RETURN p_first_name||' '||p_last_name;
ELSE
RETURN p_salutation||' '||p_first_name||' '||p_last_name;
END IF;
END;
/
SELECT format_name (salutation, first_name, last_name) student_name
FROM student
FETCH FIRST 10 ROWS ONLY;

STUDENT_NAME
-----------------
Mr. George Kocka
Ms. Janet Jung
Ms. Kathleen Mulroy
Mr. Joel Brendler
Mr. Michael Carcia
Mr. Gerry Tripp
Mr. Rommel Frost
Mr. Roger Snow
Ms. Z.A. Scrittorale
Mr. Joseph Yourish

Predefined Inquiry Directives $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE

In PL/SQL, there are a number of predefined inquiry directives, as described in the following table ($$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE are highlighted in bold):

Image

The following example demonstrates how directives may be used.

For Example Using Predefined Inquiry Directives

CREATE OR REPLACE PROCEDURE test_directives
AS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Procedure test_directives');
DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT_OWNER: '||$$PLSQL_UNIT_OWNER);
DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT_TYPE: '||$$PLSQL_UNIT_TYPE);
DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT: '||$$PLSQL_UNIT);
DBMS_OUTPUT.PUT_LINE ('$$PLSQL_LINE: '||$$PLSQL_LINE);
END;
/

BEGIN
-- Execute TEST_DERECTIVES procedure
test_directives;
DBMS_OUTPUT.PUT_LINE ('Anonymous PL/SQL block');
DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT_OWNER: '||$$PLSQL_UNIT_OWNER);
DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT_TYPE: '||$$PLSQL_UNIT_TYPE);
DBMS_OUTPUT.PUT_LINE ('$$PLSQL_UNIT: '||$$PLSQL_UNIT);
DBMS_OUTPUT.PUT_LINE ('$$PLSQL_LINE: '||$$PLSQL_LINE);
END;
/

Procedure test_directives
$$PLSQL_UNIT_OWNER: STUDENT
$$PLSQL_UNIT_TYPE: PROCEDURE
$$PLSQL_UNIT: TEST_DIRECTIVES
$$PLSQL_LINE: 8
Anonymous PL/SQL block
$$PLSQL_UNIT_OWNER:
$$PLSQL_UNIT_TYPE: ANONYMOUS BLOCK
$$PLSQL_UNIT:
$$PLSQL_LINE: 8

Compilation Parameter PLSQL_DEBUG Is Deprecated

Starting with Oracle release 12c, the PLSQL_DEBUG parameter is deprecated. To compile PL/SQL subroutines for debugging, the PLSQL_OPTIMIZE_LEVEL parameter should be set to 1. Chapter 25 covers the PLSQL_OPTIMIZE_LEVEL parameter and various optimization levels supported by the PL/SQL performance optimizer in greater detail.