Oracle 12c SQL and PL/SQL new features

SQL is the most widely used data access language while PL/SQL is a popular language that can integrate seamlessly with SQL commands. The biggest benefit of running PL/SQL is that the code processing happens natively within the Oracle Database. In the past, there have been debates and discussions on server side programming while the client invokes the PL/SQL routines to perform a task. The server side programming approach has many benefits. It reduces the network round trips between the client and the database. It reduces the code size and eases the code portability because PL/SQL can run on all platforms, wherever Oracle Database is supported.

Oracle Database 12c introduces many language features and enhancements that focus on SQL to PL/SQL integration, code migration, and ANSI compliance. This section discusses the SQL and PL/SQL new features in Oracle Database 12c.

IDENTITY columns

Oracle Database 12c introduces identity columns in SQL in compliance with the American National Standard Institute (ANSI) SQL standard. A table column, marked as IDENTITY, automatically generates an incremental numeric value at the time of record creation.

Before the release of Oracle 12c, developers had to create an additional sequence in the schema and assign its value to the column through a trigger or in a PL/SQL block. The new feature simplifies code writing and benefits the migration of a non-Oracle database to Oracle.

The following script declares an identity column in the table T_ID_COL:

/*Create a table for demonstration purpose*/
CREATE TABLE t_id_col
(id   NUMBER GENERATED AS IDENTITY,
 name VARCHAR2(20))
/

The identity column metadata can be queried from the dictionary views USER_TAB_COLS and USER_TAB_IDENTITY_COLS. Note that Oracle implicitly creates a sequence to generate the number values for the column. However, Oracle allows the configuration of the sequence attributes of an identity column. The custom sequence configuration is listed under IDENTITY_OPTIONS in USER_TAB_IDENTITY_COLS view:

/*Query identity column information in USER_TAB_COLS*/
SELECT column_name, data_default, user_generated, identity_column
FROM user_tab_cols
WHERE table_name='T_ID_COL'
/

COLUMN_NAME    DATA_DEFAULT                   USE IDE
-------------- ------------------------------ --- ---
ID             "SCOTT"."ISEQ$$_93001".nextval YES YES
NAME                                          YES NO

Let us check the attributes of the preceding sequence that Oracle has implicitly created. Note that the query uses REGEXP_SUBSTR to print the sequence configuration in multiple rows:

/*Check the sequence configuration from USER_TAB_IDENTITY_COLS view*/
SELECT table_name,column_name, generation_type, REGEXP_SUBSTR(identity_options,'[^,]+', 1, LEVEL) identity_options
FROM user_tab_identity_cols
WHERE table_name = 'T_ID_COL'
CONNECT BY REGEXP_SUBSTR(identity_options,'[^,]+',1,level)
IS NOT NULL
/

TABLE_NAME COLUMN_NAME GENERATION IDENTITY_OPTIONS
---------- ---------------------- ---------------------------------
T_ID_COL   ID          ALWAYS     START WITH: 1
T_ID_COL   ID          ALWAYS     INCREMENT BY: 1
T_ID_COL   ID          ALWAYS     MAX_VALUE: 9999999999999999999999999999
T_ID_COL   ID          ALWAYS     MIN_VALUE: 1
T_ID_COL   ID          ALWAYS     CYCLE_FLAG: N
T_ID_COL   ID          ALWAYS     CACHE_SIZE: 20
T_ID_COL   ID          ALWAYS     ORDER_FLAG: N

7 rows selected

While inserting data in the table T_ID_COL, do not include the identity column as its value is automatically generated:

/*Insert test data in the table*/
BEGIN
 INSERT INTO t_id_col (name) VALUES ('Allen');
 INSERT INTO t_id_col (name) VALUES ('Matthew');
 INSERT INTO t_id_col (name) VALUES ('Peter');
COMMIT;
END;
/

Let us check the data in the table. Note the identity column values:

/*Query the table*/
SELECT id, name FROM t_id_col
/

   ID NAME
----- --------------------
   1 Allen
   2 Matthew
   3 Peter

The sequence created under the covers for identity columns is tightly coupled with the column. If a user tries to insert a user-defined input for the identity column, the operation throws an exception ORA-32795:

INSERT INTO t_id_col VALUES (7,'Steyn');
insert into t_id_col values (7,'Steyn')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

Default column value to a sequence in Oracle 12c

Oracle Database 12c allows developers to default a column directly to a sequence?generated value. The DEFAULT clause of a table column can be assigned to SEQUENCE.CURRVAL or SEQUENCE.NEXTVAL. The feature will be useful while migrating non-Oracle data definitions to Oracle.

The DEFAULT ON NULL clause

Starting with Oracle Database 12c, a column can be assigned a default non-null value whenever the user tries to insert NULL into the column. The default value will be specified in the DEFAULT clause of the column with a new ON NULL extension.

Note that the DEFAULT ON NULL cannot be used with an object type column.

The following script creates a table t_def_cols. A column ID has been defaulted to a sequence while the column DOJ will always have a non-null value:

/*Create a sequence*/
CREATE SEQUENCE seq START WITH 100 INCREMENT BY 10
/

/*Create a table with a column defaulted to the sequence value*/
CREATE TABLE t_def_cols
( id number default seq.nextval primary key,
  name varchar2(30),
  doj date default on null '01-Jan-2000'
)
/

The following PL/SQL block inserts the test data:

/*Insert the test data in the table*/
BEGIN
  INSERT INTO t_def_cols (name, doj) values ('KATE', '27-FEB-2001');
  INSERT INTO t_def_cols (name, doj) values ('NANCY', '17-JUN-1998');
  INSERT INTO t_def_cols (name, doj) values ('LANCE', '03-JAN-2004');
  INSERT INTO t_def_cols (name) values ('MARY');
  COMMIT;
END;
/

Query the table and check the values for the ID and DOJ columns. ID gets the value from the sequence SEQ while DOJ for MARY has been defaulted to 01-JAN-2000.

/*Query the table to verify sequence and default on null values*/
SELECT * FROM t_def_cols
/

       ID  NAME     DOJ
---------- -------- ---------
       100 KATE     27-FEB-01
       110 NANCY    17-JUN-98
       120 LANCE    03-JAN-04
       130 MARY     01-JAN-00

Support for 32K VARCHAR2

Oracle Database 12c supports the VARCHAR2, NVARCHAR2, and RAW datatypes up to 32,767 bytes in size. The previous maximum limit for the VARCHAR2 (and NVARCHAR2) and RAW datatypes was 4,000 bytes and 2,000 bytes respectively. The support for extended string datatypes will benefit non-Oracle to Oracle migrations.

The feature can be controlled using the initialization parameter MAX_STRING_SIZE. It accepts two values:

• STANDARD (default)—The maximum size prior to the release of Oracle Database 12c will apply.
• EXTENDED—The new size limit for string datatypes apply. Note that after the parameter is set to EXTENDED, the setting cannot be rolled back.

The steps to increase the maximum string size in a database are:

1. Restart the database in UPGRADE mode. In the case of a pluggable database, the PDB must be opened in MIGRATE mode.
2. Use the ALTER SYSTEM command to set MAX_STRING_SIZE to EXTENDED.
3. As SYSDBA, execute the $ORACLE_HOME/rdbms/admin/utl32k.sql script. The script is used to increase the maximum size limit of VARCHAR2, NVARCHAR2, and RAW wherever required.
4. Restart the database in NORMAL mode.
5. As SYSDBA, execute utlrp.sql to recompile the schema objects with invalid status.

The points to be considered while working with the 32k support for string types are:

• COMPATIBLE must be 12.0.0.0
• After the parameter is set to EXTENDED, the parameter cannot be rolled back to STANDARD
• In RAC environments, all the instances of the database comply with the setting of MAX_STRING_SIZE

Row limiting using FETCH FIRST

For Top-N queries, Oracle Database 12c introduces a new clause, FETCH FIRST, to simplify the code and comply with ANSI SQL standard guidelines. The clause is used to limit the number of rows returned by a query. The new clause can be used in conjunction with ORDER BY to retrieve Top-N results.

The row limiting clause can be used with the FOR UPDATE clause in an SQL query. In the case of a materialized view, the defining query should not contain the FETCH clause.

Another new clause, OFFSET, can be used to skip the records from the top or middle, before limiting the number of rows. For consistent results, the offset value must be a positive number, less than the total number of rows returned by the query. For all other offset values, the value is counted as zero.

Keywords with the FETCH FIRST clause are:

• FIRST | NEXT—Specify FIRST to begin row limiting from the top. Use NEXT with OFFSET to skip certain rows.
• ROWS | PERCENT—Specify the size of the result set as a fixed number of rows or percentage of total number of rows returned by the query.
• ONLY | WITH TIES—Use ONLY to fix the size of the result set, irrespective of duplicate sort keys. If you want all records with matching sort keys, specify WITH TIES.

The following query demonstrates the use of the FETCH FIRST and OFFSET clauses in Top-N queries:

/*Create the test table*/
CREATE TABLE t_fetch_first
(empno VARCHAR2(30),
 deptno NUMBER,
 sal NUMBER,
 hiredate DATE)
/

The following PL/SQL block inserts sample data for testing:

/*Insert the test data in T_FETCH_FIRST table*/
BEGIN
  INSERT INTO t_fetch_first VALUES (101, 10, 1500, '01-FEB-2011');
  INSERT INTO t_fetch_first VALUES (102, 20, 1100, '15-JUN-2001');
  INSERT INTO t_fetch_first VALUES (103, 20, 1300, '20-JUN-2000');
  INSERT INTO t_fetch_first VALUES (104, 30, 1550, '30-DEC-2001');
  INSERT INTO t_fetch_first VALUES (105, 10, 1200, '11-JUL-2012');
  INSERT INTO t_fetch_first VALUES (106, 30, 1400, '16-AUG-2004');
  INSERT INTO t_fetch_first VALUES (107, 20, 1350, '05-JAN-2007');
  INSERT INTO t_fetch_first VALUES (108, 20, 1000, '18-JAN-2009');
  COMMIT;
END;
/

The SELECT query pulls in the top-5 rows when sorted by their salary:

/*Query to list top-5 employees by salary*/
SELECT *
FROM t_fetch_first
ORDER BY sal DESC
FETCH FIRST 5 ROWS ONLY
/
EMPNO       DEPTNO   SAL HIREDATE
--------  ------ ------- ---------
104               30    1550 30-DEC-01
101               10    1500 01-FEB-11
106               30    1400 16-AUG-04
107               20    1350 05-JAN-07
103               20    1300 20-JUN-00

The SELECT query lists the top 25% of employees (2) when sorted by their hiredate:

/*Query to list top-25% employees by hiredate*/
SELECT *
FROM t_fetch_first
ORDER BY hiredate FETCH FIRST 25 PERCENT ROW ONLY
/

EMPNO     DEPTNO  SAL  HIREDATE
--------  ------ ----- ---------
103       20     1300  20-JUN-00
102       20     1100  15-JUN-01

The SELECT query skips the first five employees and displays the next two—the 6th and 7th employee data:

/*Query to list 2 employees after skipping first 5 employees*/
SELECT *
FROM t_fetch_first
ORDER BY SAL DESC
OFFSET 5 ROWS FETCH NEXT 2 ROWS ONLY
/

Invisible columns

Oracle Database 12c supports invisible columns, which implies that a user can control the visibility of a column. A column marked invisible does not appear in the following operations:

• SELECT * FROM queries on the table
• SQL*Plus DESCRIBE command
• Local records of %ROWTYPE
• Oracle Call Interface (OCI) description

A column can be made invisible by specifying the INVISIBLE clause against the column. Columns of all types (except user-defined types), including virtual columns, can be marked invisible, provided the tables are not temporary tables, external tables, or clustered. The SELECT statement can explicitly select an invisible column. Similarly, the INSERT statement will not insert values in an invisible column unless explicitly specified.

Furthermore, a table can be partitioned based on an invisible column. A column retains its nullity feature even after it is made invisible. An invisible column can be made visible, but the ordering of the column in the table may change.

In the following script, the column NICKNAME is set as invisible in the table t_inv_col:

/*Create a table to demonstrate invisible columns*/
CREATE TABLE t_inv_col
(id NUMBER,
 name VARCHAR2(30),
 nickname VARCHAR2 (10) INVISIBLE,
 dob DATE
)
/

The information about the invisible columns can be found in user_tab_cols. Note that the invisible column is marked as hidden:

/*Query the USER_TAB_COLS for metadata information*/
SELECT column_id,
       column_name,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = 'T_INV_COL'
ORDER BY column_id
/
 COLUMN_ID COLUMN_NAME   HID
---------- ------------ ---
         1 ID            NO
         2 NAME          NO
         3 DOB           NO
           NICKNAME      YES

Hidden columns are different from invisible columns. Invisible columns can be made visible and vice versa, but hidden columns cannot be made visible.

If we try to make the NICKNAME visible and NAME invisible, observe the change in column ordering:

/*Script to change visibility of NICKNAME column*/
ALTER TABLE t_inv_col MODIFY nickname VISIBLE
/
/*Script to change visibility of NAME column*/
ALTER TABLE t_inv_col MODIFY name INVISIBLE
/
/*Query the USER_TAB_COLS for metadata information*/
SELECT column_id,
       column_name,
       hidden_column
FROM   user_tab_cols
WHERE  table_name = 'T_INV_COL'
ORDER BY column_id
/

 COLUMN_ID COLUMN_NAME   HID
---------- ------------ ---
         1 ID            NO
         2 DOB           NO
         3 NICKNAME      NO
           NAME          YES

Temporal databases

Temporal databases were released as a new feature in ANSI SQL:2011. The term temporal data can be understood as a piece of information that can be associated with a period within which the information is valid. Before the feature was included in Oracle Database 12c, data whose validity is linked with a time period had to be handled either by the application or using multiple predicates in the queries. Oracle 12c partially inherits the feature from the ANSI SQL:2011 standard to support the entities whose business validity can be bracketed with a time dimension.

The temporal database feature in Oracle Database 12c is different from the total recall feature in Oracle Database 11g. The total recall feature records the transaction time of the data in the database to secure the transaction validity and not the functional validity. For example, an investment scheme is active between January to December. The date recorded in the database at the time of data loading is the transaction timestamp.

Tip

Starting from Oracle 12c, the Total Recall feature has been rebranded as Flashback Data Archive and has been made available for all versions of Oracle Database.

The valid time temporal feature can be enabled for a table by adding a time dimension using the PERIOD FOR clause on the date or timestamp columns of the table. The following script creates a table t_tmp_db with valid time temporal:

/*Create table with valid time temporal*/
CREATE TABLE t_tmp_db(
id NUMBER,
name VARCHAR2(30),
policy_no VARCHAR2(50),
policy_term number,
pol_st_date date,
pol_end_date date,
PERIOD FOR pol_valid_time (pol_st_date, pol_end_date))
/

Create some sample data in the table:

/*Insert test data in the table*/
BEGIN
INSERT INTO t_tmp_db
VALUES (100, 'Packt', 'PACKT_POL1', 1, '01-JAN-2015', '31-DEC-2015');
INSERT INTO t_tmp_db
VALUES (110, 'Packt', 'PACKT_POL2', 2, '01-JAN-2015', '30-JUN-2015');
INSERT INTO t_tmp_db
VALUES (120, 'Packt', 'PACKT_POL3', 3, '01-JUL-2015', '31-DEC-2015');
COMMIT;
END;
/

Let us set the current time period window using DBMS_FLASHBACK_ARCHIVE. Grant the EXECUTE privilege on the package to the scott user.

/*Connect to sysdba to grant execute privilege to scott*/
conn sys/oracle as sysdba
GRANT EXECUTE ON dbms_flashback_archive to scott
/

Grant succeeded.

/*Connect to scott*/
conn scott/tiger
/*Set the valid time period as CURRENT*/
EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT');

PL/SQL procedure successfully completed.

Setting the valid time period as CURRENT means that all the tables with a valid time temporal will only list the rows that are valid with respect to today's date. You can set the valid time to a particular date too.

/*Query the table*/
SELECT * from t_tmp_db
/

       ID POLICY_NO  POL_ST_DATE             POL_END_DATE
--------- ---------- ------------------------- -------------------
       100 PACKT_POL1 01-JAN-15               31-DEC-15
       110 PACKT_POL2 01-JAN-15               30-JUN-15

Tip

Due to a dependency on the current date, the result may vary when the reader runs the preceding queries.

The query lists only those policies that are active as of March 2015. Since, the third policy starts in July 2015, it is currently not active.

In-Database Archiving

Oracle Database 12c introduces In-Database Archiving to archive the low priority data in a table. The inactive data remains in the database but is not visible to the application.

You can mark old data for archival, which is not actively required in the application except for regulatory purposes. Although the archived data is not visible to the application, it is available for querying and manipulation. In addition, the archived data can be compressed to improve backup performance.

A table can be enabled by specifying the ROW ARCHIVAL clause at the table level, which adds a hidden column ORA_ARCHIVE_STATE to the table structure. The column value must be updated to mark a row for archival. For example:

/*Create a table with row archiving*/
CREATE TABLE t_row_arch(
x number,
y number,
z number) ROW ARCHIVAL
/

When we query the table structure in the USER_TAB_COLS view, we find an additional hidden column, which Oracle implicitly adds to the table:

/*Query the columns information from user_tab_cols view*/
SELECT column_id,column_name,data_type, hidden_column
FROM user_tab_cols
WHERE table_name='T_ROW_ARCH'
/

COLUMN_ID  COLUMN_NAME          DATA_TYPE  HID
---------- ------------------ ---------- ---
           ORA_ARCHIVE_STATE  VARCHAR2   YES
       1   X                  NUMBER     NO
       2   Y                  NUMBER     NO
       3   Z                  NUMBER     NO

Let us create test data in the table:

/Insert test data in the table*/
BEGIN
  INSERT INTO t_row_arch VALUES (10,20,30);
  INSERT INTO t_row_arch VALUES (11,22,33);
  INSERT INTO t_row_arch VALUES (21,32,43);
  INSERT INTO t_row_arch VALUES (51,82,13);
  commit;
END;
/

For testing purpose, let us archive the rows in the table where X > 50 by updating the ora_archive_state column:

/*Update ORA_ARCHIVE_STATE column in the table*/
UPDATE t_row_arch
SET ora_archive_state = 1
WHERE x > 50
/
COMMIT
/

By default, the session displays only the active records from an archival-enabled table:

/*Query the table*/
SELECT *
FROM t_row_arch
/
   X      Y         Z
------ -------- ----------
  10     20        30
  11     22        33
  21     32        43

If you wish to display all the records, change the session setting:

/*Change the session parameter to display the archived records*/
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL
/

Session altered.

/*Query the table*/
SELECT *
FROM t_row_arch
/
   X      Y         Z
---------- ---------- ----------
  10     20        30
  11     22        33
  21     32        43
  51     82        13

Defining a PL/SQL subprogram in the SELECT query and PRAGMA UDF

Oracle Database 12c includes two new features to enhance the performance of functions when called from SELECT statements. With Oracle 12c, a PL/SQL subprogram can be created inline with the SELECT query in the WITH clause declaration. The function created in the WITH clause subquery is not stored in the database schema and is available for use only in the current query. Since a procedure created in the WITH clause cannot be called from the SELECT query, it can be called in the function created in the declaration section. The feature can be very handy in read-only databases where the developers were not able to create PL/SQL wrappers.

Oracle Database 12c adds the new PRAGMA UDF to create a standalone function with the same objective.

Earlier, the SELECT queries could invoke a PL/SQL function, provided the function didn't change the database purity state. The query performance would degrade because of the context switch from SQL to the PL/SQL engine (and vice versa) and the different memory representations of data type in the processing engines.

In the following example, the function fun_with_plsql calculates the annual compensation of an employee

/*Create a function in WITH clause declaration*/
WITH FUNCTION fun_with_plsql (p_sal NUMBER) RETURN NUMBER IS
BEGIN
   RETURN (p_sal * 12);
END;
SELECT ename, deptno, fun_with_plsql (sal) "annual_sal"
FROM emp
/

ENAME       DEPTNO    annual_sal
----------  --------- ----------
SMITH       20        9600
ALLEN       30        19200
WARD        30        15000
JONES       20        35700
MARTIN      30        15000
BLAKE       30        34200
CLARK       10        29400
SCOTT       20        36000
KING        10        60000
TURNER      30        18000
ADAMS       20        13200
JAMES       30        11400
FORD        20        36000
MILLER      10        15600

14 rows selected.

Note

If the query containing the WITH clause declaration is not a top-level statement, then the top level statement must use the WITH_PLSQL hint. The hint is used if INSERT, UPDATE, or DELETE statements are trying to use a SELECT with a WITH clause definition. Failure to include the hint results in an exception ORA-32034: unsupported use of WITH clause.

A function can be created with the PRAGMA UDF to inform the compiler that the function is always called in a SELECT statement. Note that the standalone function created in the following code carries the same name as the one in the last example. The local WITH clause declaration takes precedence over the standalone function in the schema.

/*Create a function with PRAGMA UDF*/
CREATE OR REPLACE FUNCTION fun_with_plsql (p_sal NUMBER)
RETURN NUMBER is
PRAGMA UDF;
BEGIN
 RETURN (p_sal *12);
END;
/

Since the objective of the feature is performance, let us go ahead with a case study to compare the performance when using a standalone function, a PRAGMA UDF function, and a WITH clause declared function.

Test setup

The exercise uses a test table with 1 million rows, loaded with random data.

/*Create a table for performance test study*/
CREATE TABLE t_fun_plsql
(id number,
 str varchar2(30))
/
/*Generate and load random data in the table*/
INSERT /*+APPEND*/ INTO t_fun_plsql
SELECT ROWNUM, DBMS_RANDOM.STRING('X', 20)
FROM dual
CONNECT BY LEVEL <= 1000000
/
COMMIT
/

• Case 1: Create a PL/SQL standalone function as it used to be until Oracle Database 12c. The function counts the numbers in the str column of the table.

  /*Create a standalone function without Oracle 12c enhancements*/
  CREATE OR REPLACE FUNCTION f_count_num (p_str VARCHAR2)
  RETURN PLS_INTEGER IS
  BEGIN
     RETURN (REGEXP_COUNT(p_str,'\d'));
  END;
  /

  The PL/SQL block measures the elapsed and CPU time when working with a pre-Oracle 12c standalone function. These numbers will serve as the baseline for our case study.

  /*Set server output on to display messages*/
  SET SERVEROUTPUT ON
  /*Anonymous block to measure performance of a standalone function*/
  DECLARE
   l_el_time PLS_INTEGER;
   l_cpu_time PLS_INTEGER;
   CURSOR C1 IS
     SELECT f_count_num (str) FROM t_fun_plsql;
   TYPE t_tab_rec IS TABLE OF PLS_INTEGER;
   l_tab t_tab_rec;
  BEGIN
   l_el_time := DBMS_UTILITY.GET_TIME ();
   l_cpu_time := DBMS_UTILITY.GET_CPU_TIME ();
   OPEN c1;
   FETCH c1 BULK COLLECT INTO l_tab;
   CLOSE c1;
   DBMS_OUTPUT.PUT_LINE ('Case 1: Performance of a standalone function');
   DBMS_OUTPUT.PUT_LINE ('Total elapsed time:'||to_char(DBMS_UTILITY.GET_TIME () - l_el_time));
   DBMS_OUTPUT.PUT_LINE ('Total CPU time:'||to_char(DBMS_UTILITY.GET_CPU_TIME () - l_cpu_time));
  END;
  /

  Performance of a standalone function:

  Total elapsed time:1559
  Total CPU time:1366
  
  PL/SQL procedure successfully completed.

• Case 2: Create a PL/SQL function using PRAGMA UDF to count the numbers in the str column.

  /*Create the function with PRAGMA UDF*/
  CREATE OR REPLACE FUNCTION f_count_num_pragma (p_str VARCHAR2)
  RETURN PLS_INTEGER IS
     PRAGMA UDF;
  BEGIN
     RETURN (REGEXP_COUNT(p_str,'\d'));
  END;
  /

  Let us now check the performance of the PRAGMA UDF function using the following PL/SQL block.

  /*Set server output on to display messages*/
  SET SERVEROUTPUT ON
  /*Anonymous block to measure performance of a PRAGMA UDF function*/
  DECLARE
   l_el_time PLS_INTEGER;
   l_cpu_time PLS_INTEGER;
   CURSOR C1 IS
     SELECT f_count_num_pragma (str) FROM t_fun_plsql;
   TYPE t_tab_rec IS TABLE OF PLS_INTEGER;
   l_tab t_tab_rec;
  BEGIN
   l_el_time := DBMS_UTILITY.GET_TIME ();
   l_cpu_time := DBMS_UTILITY.GET_CPU_TIME ();
   OPEN c1;
   FETCH c1 BULK COLLECT INTO l_tab;
   CLOSE c1;
   DBMS_OUTPUT.PUT_LINE ('Case 2: Performance of a PRAGMA UDF function');
   DBMS_OUTPUT.PUT_LINE ('Total elapsed time:'||to_char(DBMS_UTILITY.GET_TIME () - l_el_time));
   DBMS_OUTPUT.PUT_LINE ('Total CPU time:'||to_char(DBMS_UTILITY.GET_CPU_TIME () - l_cpu_time));
  END;
  /

  Performance of a PRAGMA UDF function:

  Total elapsed time:664
  Total CPU time:582
  
  PL/SQL procedure successfully completed.

• Case 3: The following PL/SQL block dynamically executes the function in the WITH clause subquery. Note that, unlike other SELECT statements, a SELECT query with a WITH clause declaration cannot be executed statically in the body of a PL/SQL block.

  /*Set server output on to display messages*/
  SET SERVEROUTPUT ON
  /*Anonymous block to measure performance of inline function*/
  DECLARE
   l_el_time PLS_INTEGER;
   l_cpu_time PLS_INTEGER;
   l_sql  VARCHAR2(32767);
   c1 sys_refcursor;
   TYPE t_tab_rec IS TABLE OF PLS_INTEGER;
   l_tab t_tab_rec;
  BEGIN
    l_el_time := DBMS_UTILITY.get_time;
    l_cpu_time  := DBMS_UTILITY.get_cpu_time;
    l_sql := 'WITH FUNCTION f_count_num_with (p_str VARCHAR2)
              RETURN NUMBER IS
              BEGIN
                 RETURN (REGEXP_COUNT(p_str,'''||'\'||'d'||'''));
              END;
              SELECT f_count_num_with(str) FROM t_fun_plsql';
    OPEN c1 FOR l_sql;
    FETCH c1 bulk collect INTO l_tab;
    CLOSE c1;
   DBMS_OUTPUT.PUT_LINE ('Case 3: Performance of an inline function');
   DBMS_OUTPUT.PUT_LINE ('Total elapsed time:'||to_char(DBMS_UTILITY.GET_TIME () - l_el_time));
   DBMS_OUTPUT.PUT_LINE ('Total CPU time:'||to_char(DBMS_UTILITY.GET_CPU_TIME () - l_cpu_time));
  END;
  /

  Performance of an inline function:

  Total elapsed time:830
  Total CPU time:718
  
  PL/SQL procedure successfully completed.

Comparative analysis

Comparing the results from the preceding three cases, it's clear that the Oracle 12c flavor of PL/SQL functions out-performs the pre-12c standalone function by a high margin. From the following matrix, it is apparent that the usage of the PRAGMA UDF or WITH clause declaration enhances the code performance by (roughly) a factor of 2.

Note

Note that the numbers may slightly differ in the reader's testing environment but you should be able to draw the same conclusion by comparing them.

The PL/SQL program unit white listing

Prior to Oracle 12c, a standalone or packaged PL/SQL unit could be invoked by all other programs in the session's schema. Oracle Database 12c allows users to prevent unauthorized access to PL/SQL program units. You can now specify the list of whitelist program units that can invoke a particular program. The PL/SQL program header or the package specification can specify the list of program units in the ACCESSIBLE BY clause in the program header. All other program units, including cross-schema references (even SYS owned objects), trying to access a protected subprogram will receive an exception, PLS-00904: insufficient privileges to access object [object name].

The feature can be very useful in an extremely sensitive development environment. Suppose, a package PKG_FIN_PROC contains the sensitive implementation routines for financial institutions, the packaged subprograms are called by another PL/SQL package PKG_FIN_INTERNALS. The API layer exposes a fixed list of programs through a public API called PKG_CLIENT_ACCESS. In order to restrict access to the packaged routines in PKG_FIN_PROC, the users can build a safety net so as to allow access to only authorized programs.

The following PL/SQL package PKG_FIN_PROC contains two subprograms—P_FIN_QTR and P_FIN_ANN. The ACCESSIBLE BY clause includes PKG_FIN_INTERNALS which means that all other program units, including anonymous PL/SQL blocks, are blocked from invoking PKG_FIN_PROC constructs.

/*Package with the accessible by clause*/
CREATE OR REPLACE PACKAGE pkg_fin_proc
ACCESSIBLE BY (PACKAGE pkg_fin_internals)
IS
        PROCEDURE p_fin_qtr;
        PROCEDURE p_fin_ann;
END;
/

Tip

The ACCESSIBLE BY clause can be specified for schema-level programs only.

Let's see what happens when we invoke the packaged subprogram from an anonymous PL/SQL block.

/*Invoke the packaged subprogram from the PL/SQL block*/
BEGIN
   pkg_fin_proc.p_fin_qtr;
END;
/
pkg_fin_proc.p_fin_qtr;
*
ERROR at line 2:
ORA-06550: line 2, column 4:
PLS-00904: insufficient privilege to access object PKG_FIN_PROC
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

Well, the compiler throws an exception as invoking the whitelisted package from an anonymous block is not allowed.

The ACCESSIBLE BY clause can be included in the header information of PL/SQL procedures and functions, packages, and object types.

Granting roles to PL/SQL program units

Before Oracle Database 12c, a PL/SQL unit created with the definer's rights (default AUTHID) always executed with the definer's rights, whether or not the invoker has the required privileges. It may lead to an unfair situation where the invoking user may perform unwanted operations without needing the correct set of privileges. Similarly for an invoker's right unit, if the invoking user possesses a higher set of privileges than the definer, he might end up performing unauthorized operations.

Oracle Database 12c secures the definer's rights by allowing the defining user to grant complementary roles to individual PL/SQL subprograms and packages. From the security standpoint, the granting of roles to schema level subprograms, provides granular control as the privileges of the invoker are validated at the time of execution.

In the following example, we will create two users: U1 and U2. The user U1 creates a PL/SQL procedure P_INC_PRICE that adds a surcharge to the price of a product by a certain amount. U1 grants the execute privilege to user U2.

Test setup

Let's create two users and give them the required privileges.

/*Create a user with a password*/
CREATE USER u1 IDENTIFIED BY u1
/

User created.

/*Grant connect privileges to the user*/
GRANT CONNECT, RESOURCE TO u1
/

Grant succeeded.

/*Create a user with a password*/
CREATE USER u2 IDENTIFIED BY u2
/

User created.

/*Grant connect privileges to the user*/
GRANT CONNECT, RESOURCE TO u2

/

Grant succeeded.

The user U1 contains the PRODUCTS table. Let's create and populate the table.

/*Connect to U1*/
CONN u1/u1
/*Create the table PRODUCTS*/
CREATE TABLE products
(
   prod_id     INTEGER,
   prod_name   VARCHAR2(30),
   prod_cat    VARCHAR2(30),
   price       INTEGER
)
/

/*Insert the test data in the table*/
BEGIN
  DELETE FROM products;
  INSERT INTO products VALUES (101, 'Milk', 'Dairy', 20);
  INSERT INTO products VALUES (102, 'Cheese', 'Dairy', 50);
  INSERT INTO products VALUES (103, 'Butter', 'Dairy', 75);
  INSERT INTO products VALUES (104, 'Cream', 'Dairy', 80);
  INSERT INTO products VALUES (105, 'Curd', 'Dairy', 25);
  COMMIT;
END;
/

The procedure p_inc_price is designed to increase the price of a product by a given amount. Note that the procedure is created with the definer's rights.

/*Create the procedure with the definer's rights*/
CREATE OR REPLACE PROCEDURE p_inc_price
(p_prod_id NUMBER, p_amt NUMBER)
IS
BEGIN
 UPDATE products
 SET price = price + p_amt
 WHERE prod_id = p_prod_id;
END;
/

The user U1 grants execute privilege on p_inc_price to U2.

/*Grant execute on the procedure to the user U2*/
GRANT EXECUTE ON p_inc_price TO U2
/

The user U2 logs in and executes the procedure P_INC_PRICE to increase the price of Milk by 5 units.

/*Connect to U2*/
CONN u2/u2
/*Invoke the procedure P_INC_PRICE in a PL/SQL block*/
BEGIN
 U1.P_INC_PRICE (101,5);
 COMMIT;
END;
/

PL/SQL procedure successfully completed.

The last code listing exposes a gray area. The user U2, though not authorized to view PRODUCTS data, manipulates its data with the definer's rights.

We need a solution to the problem. The first step is to change the procedure from definer's rights to invoker's rights.

/*Connect to U1*/
CONN u1/u1
/*Modify the privilege authentication for the procedure to invoker's rights*/
CREATE OR REPLACE PROCEDURE p_inc_price
(p_prod_id NUMBER, p_amt NUMBER)
AUTHID CURRENT_USER
IS
BEGIN
 UPDATE products
 SET price = price + p_amt
 WHERE prod_id = p_prod_id;
END;
/

Now, if we execute the procedure from U2, it throws an exception because it couldn't find the PRODUCTS table in its schema.

/*Connect to U2*/
CONN u2/u2
/*Invoke the procedure P_INC_PRICE in a PL/SQL block*/
BEGIN
 U1.P_INC_PRICE (101,5);
 COMMIT;
END;
/
BEGIN
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "U1.P_INC_PRICE", line 5
ORA-06512: at line 2

In a similar scenario in the past, the database administrators could have easily granted select or updated privileges to U2, which is not an optimal solution from a security standpoint. Oracle 12c allows users to create program units with invoker's rights but grant the required roles to the program units and not the users. So, an invoker right unit executes with invoker's privileges, plus the PL/SQL program role.

Let's check out the steps to create a role and assign it to the procedure. SYSDBA creates the role and assigns it to the user U1. Using the ADMIN or DELEGATE option with the grant enables the user to grant the role to other entities.

/*Connect to SYSDBA*/
CONN sys/oracle as sysdba
/*Create a role*/
CREATE ROLE prod_role
/
/*Grant role to user U1 with delegate option*/
GRANT prod_role TO U1 WITH DELEGATE OPTION
/

Now, user U1 assigns the required set of privileges to the role. The role is then assigned to the required subprogram. Note that only roles, and not individual privileges, can be assigned to the schema level subprograms.

/*Connect to U1*/
CONN u1/u1
/*Grant SELECT and UPDATE privileges on PRODUCTS to the role*/
GRANT SELECT, UPDATE ON PRODUCTS TO prod_role
/
/*Grant role to the procedure*/
GRANT prod_role TO PROCEDURE p_inc_price
/

User U2 tries to execute the procedure again. The procedure is successfully executed which means the value of "Milk" has been increased by 5 units.

/*Connect to U2*/
CONN u2/u2
/*Invoke the procedure P_INC_PRICE in a PL/SQL block*/
BEGIN
 U1.P_INC_PRICE (101,5);
 COMMIT;
END;
/

PL/SQL procedure successfully completed.

User U1 verifies the result with a SELECT query.

/*Connect to U1*/
CONN u1/u1
/*Query the table to verify the change*/
SELECT *
FROM products
/

   PROD_ID PROD_NAME  PROD_CAT   PRICE
---------- ---------- ---------- ----------
       101 Milk       Dairy        25
       102 Cheese     Dairy        50
       103 Butter     Dairy        75
       104 Cream      Dairy        80
       105 Curd       Dairy        25

Miscellaneous PL/SQL enhancements

Besides the preceding key features, there are a lot of new features in Oracle 12c. The list of features is as follows:

• An invoker rights function can be result-cached—Prior to Oracle Database 12c onlythe definers' programs were allowed to cache their results. Oracle 12c adds the invoking user's identity to the result cache to make it independent of the definer.
• The compilation parameter PLSQL_DEBUG has been deprecated.
• Two conditional compilation inquiry directives $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE have been implemented.