In this article by Saurabh K. Gupta, author of the book Oracle Advanced PL/SQL Developer Professional Guide, Second Edition you will learn new features in Oracle 12c SQL and PL/SQL.
(For more resources related to this topic, see here.)
Oracle 12c SQL and PL/SQL new features
SQL is the most widely used data access language while PL/SQL is an exigent 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 which are focused 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.
Oracle Database 12c Release 1 introduces identity columns in the tables in compliance with the American National Standard Institute (ANSI) SQL standard. A table column, marked as IDENTITY, automatically generate an incremental numeric value at the time of record creation.
Before the release of Oracle 12c, developers had to create an additional sequence object in the schema and assign its value to the column. 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_COLSand 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 the 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:
- Restart the database in UPGRADE mode. In the case of a pluggable database, the PDB must be opened in MIGRATEmode.
- Use the ALTER SYSTEM command to set MAX_STRING_SIZE to EXTENDED.
- 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.
- Restart the database in NORMAL mode.
- 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 126.96.36.199
- 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 a 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 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 /
Oracle Database 12c supports invisible columns, which implies that 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 ones. An invisible column can be explicitly selected by the SELECT statement. Similarly, the INSERTstatement 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 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.
If you’re relating a temporal database with the Oracle Database 11g Total Recall feature, you’re wrong. 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.
The valid time temporal 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 the 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 / 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
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.
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 used to degrade because of the context switch from SQL to the PL/SQL engine (and vice versa) and the different memory representations of data type representation in the processing engines.
In the following example, the function fun_with_plsql calculates the annual compensation of an employee’s monthly salary:
/*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.
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 WITHclause declared function.
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.
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.
|Case Description||Elapsed Time||CPU time||Performance gain factor by CPU time|
|Standalone PL/SQL function in pre-Oracle 12c database||1559||1336||1x|
|Standalone PL/SQL PRAGMA UDF function in Oracle 12c||664||582||2.3x|
|Function created in WITH clause declaration in Oracle 12c||830||718||1.9x|
The PL/SQL program unit whitelisting
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; /
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.
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 the security standpoint. Oracle 12c allows the 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 / 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—until Oracle 11g, only the 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.
This chapter covers the top rated and new features of Oracle 12c SQL and PL/SQL as well as some miscellaneous PL/SQL enhancements.
This chapter covers the top rated and new features of Oracle 12c SQL and PL/SQL as well as some miscellaneous PL/SQL enhancements.
Further resources on this subject: