Skip to main content

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
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
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.
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!

Comments

  1. Steven:

    There is a compile-time error in your example for using DBMS_SQL to bind PL/SQL Datatypes.

    The datatype rec_t.rec must be declared FIRST before using it as a datatype for variable v1.

    ReplyDelete
    Replies
    1. Many thanks! I neglected to include the previous step from my LiveSQL script when I moved the code over.

      Delete
  2. No worries Steven (by the way I couldn't logon using my personal Google account from work but I can from home). It's Giovanni. I'm studying for the OCP 12c: Advanced PL/SQL exam and one of the sub-topics is "Use PL/SQL Bind Types". So I noticed your recent blog about it and noticed the error. Unfortunately Oracle 12.2 for Windows is not available for download yet so I can't play around just yet.

    Thanks,
    GJ

    ReplyDelete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel