A Roundup of New PL/SQL Features in Oracle Database 12c Release 2

I've been publishing Oracle Magazine articles, blog posts and LiveSQL scripts on new PL/SQL features in Oracle Database 12c Release 2 (there, are those enough hyperlinks?). As have others.

I thought it might be helpful to provide a single reference post from which you could check out all the others.

I also include links to content from other experts who have posted on the same topics. I will update this post as more resources are published.

First some overview articles that you will find as solid starting points:

12 Things Developers Will Love About Oracle Database 12c Release 2, covering SQL and PL/SQL features, from Chris Saxon. It even comes with an infographic!

The Power of Cloud PL/SQL, my Oracle Magazine roundup article. No, I did not choose the title of the article.

And now for specific enhancements....

PL/Scope Discovers SQL!

PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) and SQL statements (as of 12.2) in your PL/SQL code. You can do all sorts of amazing deep-dive analysis of your code with PL/Scope, answering questions like:

• Where is a variable assigned a value in a program? 
• What variables are declared inside a given program? 
• Which programs call another program (that is, you can get down to a subprogram in a package)? 
• Find the type of a variable from its declaration. 
• [New in 12.2] Show where specific columns are referenced. 
• [New in 12.2] Locate all SQL statements containing hints. 
• [New in 12.2] Find all dynamic SQL usages – ideal for getting rid of SQL injection vulnerabilities. 
• [New in 12.2] Show all locations in your code where you commit or rollback. 
• [New in 12.2] And my latest favorite: Locate multiple appearances of same "canonical" SQL statement.

Example: Show all program units with same SQL in more than one place

SELECT object_name, 
       line, 
       text 
  FROM user_statements 
 WHERE sql_id IN (  SELECT sql_id 
                      FROM user_statements 
                     WHERE sql_id IS NOT NULL 
                  GROUP BY sql_id 
                    HAVING COUNT (*) > 1) 
 ORDER BY object_name, line

Powerful Impact Analysis: Oracle Magazine article on PL/Scope's ability to analyze SQL statements in your PL/SQL code.

Impact Analysis with PL/Scope: a presentation on PL/Scope for 12.2, offered to you on SlideShare.

Find duplicate SQL statements with PL/Scope in 12.2, from this blog.

LiveSQL Scripts

• PL/Scope Now Analyzes SQL Statements!
• Find duplicate SQL statements with PL/Scope
• Use PL/Scope to Find All SQL Statements with Hints

ACCESSIBLE BY clause Enhancements

The ACCESSIBLE BY clause specifies a list of PL/SQL units that are considered safe to invoke the subprogram, and blocks all others.

Starting with Oracle Database 12c release 2 (12.2), the accessor list can be defined on individual subprograms in a package. This list is checked in addition to the accessor list defined on the package itself (if any). This list may only restrict access to the subprogram – it cannot expand access. This code management feature is useful to prevent inadvertent use of internal subprograms. For example, it may not be convenient or feasible to reorganize a package into two packages: one for a small number of procedures requiring restricted access, and another one for the remaining units requiring public access.

Example: Different ways to specify whitelists at subprogram level

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
IS 
   PROCEDURE do_this; 
 
   PROCEDURE this_for_proc_only 
      ACCESSIBLE BY (PROCEDURE generic_name); 
 
   PROCEDURE this_for_trigger_only 
      ACCESSIBLE BY (TRIGGER generic_name); 
 
   PROCEDURE this_for_any_generic_name 
      ACCESSIBLE BY (generic_name); 
END;

Enhanced Whitelist Management in 12.2

LiveSQL script: Enhanced ACCESSIBLE_BY (Whitelisting) Options

PL/SQL Expressions Enhancements

Starting with Oracle Database 12c release 2 (12.2), expressions may be used in declarations where previously only literal constants were allowed. Static expressions can now be used in subtype declarations.

The definition of static expressions is expanded to include all the PL/SQL scalar types and a much wider range of operators. Character operands are restricted to a safe subset of the ASCII character set. Operators whose results depend on any implicit NLS parameter are disallowed.

Expanded and generalized expressions have two primary benefits for PL/SQL developers: (1) Programs are much more adaptable to changes in their environment. (2) Programs are more compact, clearer, and substantially easier to understand and maintain.

Example: Soft-coding VARCHAR2 length

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
IS 
   c_max_length constant integer := 32767; 
   SUBTYPE maxvarchar2 IS VARCHAR2 (c_max_length); 
END;  
/

DECLARE 
   l_big_string1 VARCHAR2 (pkg.c_max_length) := 'So big....'; 
   l_big_String2 pkg.maxvarchar2 := 'So big via packaged subtype....'; 
   l_half_big VARCHAR2 (pkg.c_max_length / 2) := 'So big....'; 
BEGIN    
   DBMS_OUTPUT.PUT_LINE (l_big_string1); 
   DBMS_OUTPUT.PUT_LINE (l_big_string2); 
END;
/

Avoid hard-coding maximum length of VARCHAR2 (and more), from this blog

LiveSQL script: Use static expressions

Support for SQL JSON operators in PL/SQL

This feature makes it easier to work with JSON documents stored in an Oracle Database and to generate JSON documents from relational data.

Oracle Database support for storing and querying JSON documents in the database is extended by the addition of new capabilities, including the ability to declaratively generate JSON documents from relational data using SQL and the ability to manipulate JSON documents as PL/SQL objects. SQL JSON operators are supported in PL/SQL with a few exceptions.

I'ver barely gotten started with JSON in PL/SQL, so I will add more links here soon, but in the meantime, some excellent resources from Arup Nanda and Tim Hall:

JSON in Motion by Arup Nanda

JSON Support in Oracle Database 12c Release 2 (12.2) by Tim Hall

Support for Longer Identifiers

The maximum length of all identifiers used and defined by PL/SQL is increased to 128 bytes, up from 30 bytes in previous releases. If the COMPATIBLE parameter is set to a value of 12.2.0 or higher, the representation of the identifier in the database character set cannot exceed 128 bytes. If the COMPATIBLE parameter is set to a value of 12.1.0 or lower, the limit is 30 bytes.

A new function ORA_MAX_NAME_LEN_SUPPORTED has been introduced to check this limit.

EXEC DBMS_OUTPUT.PUT_LINE(ORA_MAX_NAME_LEN_SUPPORTED);
128

A new constant ORA_MAX_NAME_LEN defines the name length maximum. New subtypes DBMS_ID and DBMS_QUOTED_ID define the length of identifiers in objects for SQL, PL/SQL and users.

LiveSQL script:  Identifiers can now be up to 128 bytes in length!

PL/SQL Deprecation Pragma

The DEPRECATE pragma marks a PLSQL program element as deprecated. The compiler warnings tell users of a deprecated element that other code may need to be changed to account for the deprecation.

Example: Marking a package as deprecated

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
AS 
   PRAGMA DEPRECATE(pkg); 
 
   PROCEDURE proc; 
   FUNCTION func RETURN NUMBER; 
END;

12.2 Helps You Manage Persistent Code Base w/New Deprecate Pragma, from this blog

LiveSQL script: Use DEPRECATE Pragma to Document Deprecated Units

DBMS_SQL Binding to PL/SQL Datatypes

With 12.2, DBMS_SQL catches up with 12.1 enhancements for native dynamic SQL (EXECUTE IMMEDIATE) in its support for user-defined PL/SQL datatypes. Now you can bind records and associative arrays....and Booleans!

Oracle Magazine article: Get Up to Speed with DBMS_SQL

LiveSQL script: DBMS_SQL Binding to PL/SQL Datatypes

Example

CREATE OR REPLACE PACKAGE rec_t 
   AUTHID DEFINER  
AS 
   TYPE rec1 IS RECORD (n NUMBER); 
 
   TYPE rec IS RECORD 
   ( 
      n    NUMBER, 
      n1   NUMBER 
   ); 
END rec_t;
/

DECLARE 
/* Bind record as IN */ 
   stmt_1   CONSTANT VARCHAR2 (2000) 
      := q'[ 
DECLARE 
   v2   rec_t.rec; 
BEGIN 
   v2 := :v1; 
   DBMS_OUTPUT.put_line ( 
      'rec.n =  ' || v2.n || '  rec.n1 =  ' || v2.n1); 
END;]' ; 
 
   dummy             NUMBER; 
   cur               NUMBER; 
   v1                rec_t.rec; 
BEGIN 
   v1.n := 100; 
   v1.n1 := 200; 
 
   cur := DBMS_SQL.open_cursor (); 
   DBMS_SQL.parse (cur, stmt_1, DBMS_SQL.native); 
 
   /* Remember: with dynamic PL/SQL blocks, you bind by name, not position. */ 
   DBMS_SQL.bind_variable_pkg (cur, 'v1', v1); 
 
   dummy := DBMS_SQL.execute (cur); 
   DBMS_SQL.close_cursor (cur); 
END;

Altogether, a whole lot of useful stuff for you to review and think about how it can be applied in your applications and in your development environments. Enjoy!