9 new(er) features of Oracle PL/SQL

Part 13 in a 12-part series of articles on understanding and using PL/SQL for accessing Oracle Database

[We recently republished Steven Feuerstein’s incredibly popular 12-part series on PL/SQL—which was originally written in 2011. Now, a decade later, we asked Steven to write a 13^th part to explain what’s new and different since the original series. He chose nine significant features to highlight for PL/SQL developers. —Ed.]

What’s new in Oracle PL/SQL since 2011? Plenty. Any programming language that is actively used is also going to be actively repaired and enhanced. In this article, I review some of the most recent and important enhancements to PL/SQL. Given its maturity, as you might expect, these enhancements primarily fill in “gaps” in prior functionality in key areas and improve developer productivity.

Bear in mind that Oracle PL/SQL has been around since 1991—that’s 30 years. That’s one heck of a long time for a technology to mature and stay relevant. PL/SQL has done both really well, and today it’s a mature and powerful language: All major features needed to use PL/SQL to build and manage APIs to our data and business logic are solidly in place. What’s more, the language is still relevant: PL/SQL is at the heart of hundreds of thousands of applications that are used by billions of human beings.

That said, over the past three decades, PL/SQL has continued to evolve. Here are eight key updates and improvements to PL/SQL since my original tutorial series was published 10 years ago.

Use qualified expressions for collections and records

Ever since Oracle Database introduced the object-relational model in version 8, PL/SQL developers have been able to use constructor functions to, well, construct nested tables, varrays, and object type instances. A constructor function is a function created implicitly by Oracle Database that has the same name as its type. The easiest way to understand constructor functions is to look at an example.

To initialize a nested table of integers with three elements, I can do this:

DECLARE
   TYPE numbers_t IS TABLE OF NUMBER;
   l_numbers numbers_t := numbers_t (1, 2, 3 * 3);
BEGIN
   DBMS_OUTPUT.put_line (l_numbers.COUNT);
END;

In this case, numbers_t is the name of the type, but it is also the name of a function. For a nested table or varray type, I simply pass in a list of expressions, and the function returns a collection populated with those values. If I don’t pass in any values and simply call the constructor function, the collection will be initialized but will be empty.

But before Oracle Database 18c, if I wanted to use an associative array (in other words, an index-by table), this direct functionality was not allowed. Instead, I had to assign elements to the array, one at a time, as in

DECLARE
   TYPE numbers_t IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   l_numbers numbers_t;
BEGIN
   l_numbers (1) := 100;
   l_numbers (2) := 1000;
   l_numbers (3) := 10000;
END;

I had the same challenge with populating values of fields in a record: Either I wrote my own function to create and return a record, or I wrote field-by-field assignments in the executable section of my block. Here’s an example:

DECLARE
   TYPE person_rt IS 
      RECORD (last_name VARCHAR2(100), hair_color VARCHAR2(100));
   l_person person_rt;
BEGIN
   l_person.last_name := 'Feuerstein';
   l_person.hair_color := 'Not Applicable';
END;

That’s cumbersome, irritating, and as of Oracle Database 18c, you and I don’t have to bother with that sort of thing anymore. Now, any PL/SQL value can be provided by a qualified expression, just like a constructor provides an abstract data type value.

PL/SQL uses the terms qualified expression and aggregate rather than the SQL term type constructor, but the functionality is the same. Qualified expressions improve program clarity and developer productivity by providing the ability to declare and define a complex value in a compact form where the value is needed.

Here’s a block of code that demonstrates qualified expressions for both records and associative arrays:

DECLARE 
   TYPE species_rt IS RECORD ( 
      species_name           VARCHAR2 (100), 
      habitat_type           VARCHAR2 (100), 
      surviving_population   INTEGER); 
 
   l_elephant   species_rt 
      := species_rt (species_name           => 'Elephant', 
                     surviving_population   => '10000', 
                     habitat_type           => 'Savannah'); 
BEGIN 
   DBMS_OUTPUT.put_line ('Species: ' || l_elephant.species_name); 
END;

See these resources:

• Easy initializing for records and arrays
• Qualified expressions (aka, constructor functions) for collections and records in 18c
• Live SQL code library: Qualified expressions

Analyze SQL statements in PL/SQL with PL/Scope

PL/Scope, first introduced in Oracle Database 11g, made it possible to perform SQL-based analysis of your PL/SQL code. PL/Scope is invoked by the compiler to collect information about all the identifiers in your PL/SQL program unit. This information is then made available through a set of static data dictionary views. The views explain the different ways a variable is used in a declaration, definition, reference, call, or assignment, as well as the location of each usage in the program unit’s source code.

Right out of the gate, PL/Scope helped developers perform code analysis of a quality and quantity never before possible. And then, in Oracle Database 12c Release 2, PL/Scope got even more useful by also analyzing and collecting information about the SQL statements in your PL/SQL code.

It’s out of the scope of this article to explore all the amazing things you can do with PL/Scope, so I will offer up my favorite: finding duplicate SQL statements in your PL/SQL code.

PL/SQL makes it so easy to write SQL that I sometimes worry that the language makes it too easy, by which I mean that some database developers don’t pay enough attention to the SQL part of their PL/SQL code. They write or copy and paste the same or similar statements throughout their code. Then when it’s time to maintain or optimize that SQL, how can they be sure they have found and updated all instances?

To make things more complicated, two statements that appear to be quite different might actually turn out to be the same. Consider the following two procedures:

PROCEDURE proc1 (p_id NUMBER, p_name OUT VARCHAR2) 
IS 
BEGIN 
   select  
          last_name 
     into  
          p_name 
     from  
          employees 
    where     
          employee_id = p_id; 
END;

PROCEDURE proc2 (id_in NUMBER, name_out OUT VARCHAR2) 
IS 
BEGIN 
   SELECT last_name 
     INTO name_out 
     FROM EMPLOYEES 
    WHERE employee_id = id_in; 
END;

These two SQL statements are physically distinct but not logically distinct. The differences have to do with whitespace and uppercase versus lowercase keywords and identifiers. If these two queries were executed outside of a PL/SQL program unit, the SQL engine would in fact consider them two different cursors and parse both of them. If, on the other hand, they were both physically identical, as shown below, the SQL engine would parse only once because it would be able—with a quick hash comparison—to determine that they were the same:

SELECT LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1

Ah, but here comes the wonder of PL/SQL! Whenever you compile a program unit (package, procedure, function, and trigger), the compiler automatically canonicalizes any static SQL by removing extra whitespace and converting all keywords and identifiers to uppercase, as appropriate. As a result, when the code in these two procedures is executed, the SQL engine sees that they are actually the same and avoids unnecessary parsing.

Well, that’s all very wonderful, but still I am left with functionally duplicate SQL in my codebase, even if it’s not visually identical. That’s where PL/Scope comes to the rescue. If I execute this statement and then compile those two procedures as follows:

ALTER SESSION SET plscope_settings='identifiers:all, statements:all'

I can then see which statements have been recorded by PL/Scope:

SELECT signature, sql_id, text 
    FROM user_statements 
   WHERE object_name IN ('PROC1', 'PROC2') 
ORDER BY line, col

Here’s what is returned:

SIGNATURE                         SQL_ID         TEXT
91D8E98E6FEF15BD6BDC24D643F30119  641rpxvq1qu8n  SELECT LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1 
E5FF98F221F6E2D698BC97BF6292ADAD  641rpxvq1qu8n  SELECT LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1

As you can see, the values in the SIGNATURE column are different: That column contains a value generated by PL/Scope for each statement and is guaranteed to be unique. But notice that the values in the SQL_ID and TEXT columns are the same.

Now I can take advantage of both PL/SQL canonicalization and PL/Scope analysis to identify duplicates, for example:

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

And here’s the result:

SQL_ID         TEXT                                                      COUNT(*)
641rpxvq1qu8n  SELECT LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1   2

I can also easily identify the location of these duplicates, for example:

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

OBJECT_NAME  LINE  TEXT
PROC1  4  SELECT LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1 
PROC2  4  SELECT LAST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1

That is a lot of analytical power, made very accessible by this very cool tool.

See these resources:

• Zoom in on your code
• Powerful impact analysis
• The PL/Scope resource center
• Live SQL code library: PL/Scope

Control access to code with the ACCESSIBLE_BY clause

Before Oracle Database 12c, PL/SQL could not prevent a session from using any and all subprograms in packages to which that session’s schema had been granted EXECUTE privileges. Developers had to accept that violations of their subprograms would or could occur or else write code to restrict access.

As of Oracle Database 12c, you can now include a clause in the header of your program unit that specifies an approved list of other PL/SQL units that can access the PL/SQL unit you are creating or altering. This is the ACCESSIBLE_BY clause.

You add this clause to the header of your package (or, starting with Oracle Database 12c Release 12.2, even subprograms within the package) and provide a list of the program units that are accessible:

CREATE PACKAGE my_package
   ACCESSIBLE BY (your_package)
IS
   PROCEDURE my_proc;
END;
/

If anything besides a subprogram in your_package calls my_package.my_proc, you will see this compilation error:

PLS-00904: insufficient privilege to access object MY_PACKAGE

One use-case for this feature is to improve manageability of your codebase. It’s quite common for packages to grow very large over time, as more and more functionality is stuffed into them.

Those packages can then become development chokepoints. Multiple developers need to work on different parts of the package. But only one can edit and compile at a time. Also, as the packages grow in size, compile time increases.

A way to resolve this is to break up that big package into multiple smaller packages. Those smaller packages will often contain what were previously private subprograms, which were not listed in the package specification. Now those smaller packages do become available through the specification, but they were never intended for general, public access. That’s a recipe for disaster.

You can then use the ACCESSIBLE_BY clause to restrict how these reorganized subprograms can be accessed.

See these resources:

• When packages need to lose weight
• Enhanced code accessibility management in 12.2
• Live SQL code library: ACCESSIBLE_BY

Reference PL/SQL-only data types in SQL statements

Let’s be clear: SQL data types are also PL/SQL data types. But not all PL/SQL data types are valid SQL types, including BOOLEAN, associative arrays, and user-defined record types. In the past, this has led to restrictions on what kind of PL/SQL functions can be invoked inside SQL and what types of data could be bound into dynamically executed PL/SQL blocks.

Oracle Database 12c Release 1 extended support for PL/SQL-specific data types in SQL statements. It’s now possible to bind values with PL/SQL-only data types to anonymous blocks (which are SQL statements), PL/SQL function calls in SQL queries and CALL statements, and the TABLE operator in SQL queries.

Here are two examples.

1. I can reference a BOOLEAN inside SQL!

CREATE OR REPLACE FUNCTION uc_last_name (  
   employee_id_in   IN employees.employee_id%TYPE,  
   upper_in         IN BOOLEAN)  
   RETURN employees.last_name%TYPE  
IS  
   l_return   employees.last_name%TYPE;  
BEGIN  
   SELECT last_name  
     INTO l_return  
     FROM employees  
    WHERE employee_id = employee_id_in;  
  
   RETURN CASE WHEN upper_in THEN UPPER (l_return) ELSE l_return END;  
END; 
/

DECLARE 
   b BOOLEAN := TRUE; 
BEGIN  
   FOR rec IN (SELECT uc_last_name (employee_id, b) lname  
                 FROM employees  
                WHERE department_id = 10)  
   LOOP  
      DBMS_OUTPUT.put_line ('Name = '|| rec.lname);  
   END LOOP;  
END; 
/

Name = WHALEN

2. I can use the TABLE operator in SQL with associative arrays, even associative arrays of records!

CREATE OR REPLACE PACKAGE pkg  
   AUTHID DEFINER  
AS  
   TYPE rec IS RECORD  
   (  
      f1   NUMBER,  
      f2   VARCHAR2 (30)  
   );  
  
   TYPE mytab IS TABLE OF rec  
      INDEX BY PLS_INTEGER;  
END; 
/

DECLARE  
   v1   pkg.mytab;    
   v2   pkg.rec;  
   c1   SYS_REFCURSOR;  
BEGIN  
   OPEN c1 FOR SELECT * FROM TABLE (v1);  
  
   FETCH c1 INTO v2;  
  
   CLOSE c1;  
END; 
/

See these resources:

• SQL in PL/SQL enhancements
• Live SQL code library: Binding PL/SQL types in SQL (12.1 and higher)

Optimize function execution in SQL

It’s really wonderful that you can call your own user-defined functions in SQL. It’s not quite as wonderful that each time you do so, Oracle Database executes a context switch from the SQL engine to the PL/SQL engine, automatically performing a variety of steps such as data type conversions when necessary.

The downside is that context switching’s overhead can have a substantial performance impact, which is why PL/SQL has bulk processing features such as FORALL and BULK COLLECT.

You can reduce the cost of a context switch to make a function call in SQL in two ways:

• The UDF pragma
• The WITH FUNCTION clause

The UDF pragma is a compiler directive that says the following, in effect: “I only or almost always call this function from within SQL. Therefore, do at compile time some of the steps you would usually do for the context switch at runtime.”

There’s literally nothing for you to do except add the pragma, for example:

CREATE OR REPLACE FUNCTION my_func RETURN DATE
IS
   PRAGMA UDF;

The PL/SQL compiler will take it from there. The performance impact will vary according to the types of parameters you have in your function. Note that there is still a context switch, but the runtime overhead cost is reduced.

You can also define your function inside a SELECT statement using the WITH clause. You can even define procedures inside the SELECT, but those procedures can only be called by a PL/SQL function defined in the same way.

Here’s an example:

WITH 
     PROCEDURE showit IS BEGIN NULL; END;
     
     FUNCTION full_name (fname_in IN VARCHAR2, lname_in IN VARCHAR2)
        RETURN VARCHAR2
     IS
     BEGIN
        showit;
        RETURN fname_in || ' ' || lname_in;
     END;

SELECT full_name (first_name, last_name)
  FROM employees
/

As with UDF, there is still a context switch, but the runtime cost is reduced.

The WITH clause can be most helpful when a formula appears multiple times in the same SQL statement and you’d like to avoid the repetition. It’s also useful when you are running queries in a read-only database and you, therefore, simply can’t create functions or any other PL/SQL program units within that database.

See this resource:

• Speed up execution of your functions inside SQL statements with UDF pragma

Get error stack, execution call stack, and error backtrace data

There are several questions that developers like to get answered when they are tracing code execution or they are otherwise trying to figure out why an error occurred:

• How did I get here? Which sequence of subprogram invocations did the application follow to get to this point?
• What’s the error information? What’s the error code, the error message, and even maybe the error stack? An error stack occurs as an exception propagates up through the call stack.
• Where did the error take place? In which subprogram? And at which line in the subprogram?

In earlier releases of Oracle Database, you answered these questions by calling the following DBMS_UTILITY functions:

DBMS_UTILITY.FORMAT_CALL_STACK: "How did I get here?"

DBMS_UTILITY.FORMAT_ERROR_STACK: "What was the error?"

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE: "Where did the error take place?"

And, of course, you can continue to do so. But you can also use the new UTL_CALL_STACK package, which provides information about currently executing subprograms. Although the package name sounds as though it provides information only about the execution call stack, it also offers access to the error stack and error backtrace data.

Each stack contains depths (locations), and you can ask for the information at a certain depth in each of the three types of stacks made available through the package. This means that you no longer have to parse the formatted strings to find the specific information you need.

One of the greatest improvements of UTL_CALL_STACK over DBMS_UTILITY.FORMAT_CALL_STACK is that you can obtain a unit-qualified name, which concatenates the unit name, all lexical parents of the subprogram, and the subprogram name. This additional information is not available, however, for the error backtrace.

See these resources:

• Sophisticated call stack analysis
• Three ways to get error message/stack in PL/SQL
• Live SQL code library: UTL_CALL_STACK

Stop privilege escalation and gain access management for program units

Oracle Database has added a small boatload of privilege- and security-related features to PL/SQL in Oracle Database 12c and beyond. These include the following.

Avoiding privilege escalation. Use the INHERIT [ANY] PRIVILEGES privilege to make it impossible for a lower-privileged user to take advantage of a higher-privileged user via an invoker rights unit.

Code-based access control. Fine-tune access to database objects inside program units by granting roles to program units rather than—or in addition to—roles granted to schemas.

When a user executes invoker rights ( AUTHID CURRENT_USER) on a program unit, that unit executes under the privileges of the invoker. If that unit also contains dynamic SQL, privilege escalation (having the authority to do more than should be allowed) is then possible.

You can stop privilege escalation by revoking the ability to, well, inherit those unneeded privileges.

As for code-based access control, Oracle Database has always offered a very robust security mechanism, because you can access only objects you own or those to which you were granted access. Within a PL/SQL program unit, you can choose the definer rights model (a user executes your code with your privileges) or the invoker rights model (a user executes your code with their privileges).

However, the granularity of this mechanism operates at the schema level, making it difficult to apply the least privilege principle in some cases.

With Oracle Database 12c, you can now restrict privileges as tightly as you would like, right down to the individual program unit, by granting roles to program units, not just to schemas. Let’s explore this feature for both definer rights and invoker rights program units. Here’s an example to give you a sense of what is possible.

First, create a role from a schema with the authority to do so, and grant it the CREATE TABLE privilege, for example:

CREATE ROLE create_table_role
/

GRANT CREATE TABLE TO create_table_role
/

Next, grant the role to the procedure. This can be done as SYSDBA. It can also be done from the HR schema, if the role is granted to HR with the admin option. Here’s the grant done as SYSDBA:

GRANT create_table_role TO PROCEDURE hr.create_table
/

To grant from HR, execute this as SYSDBA:

GRANT create_table_role TO hr WITH ADMIN OPTION
/

ALTER USER hr DEFAULT ROLE ALL EXCEPT create_table_role
/

Then execute the grant from HR:

GRANT create_table_role TO PROCEDURE create_table
/

And now you can execute the procedure and successfully create the table:

BEGIN
   create_table ('my_table');
END;
/

PL/SQL procedure successfully completed.

But if you try to create the table directly, you’ll see the same, earlier privileges error:

CREATE TABLE my_table2 (n NUMBER)
/

ERROR at line 1: ORA-01031: insufficient privileges

The only way to create a table from the HR schema is by calling this one procedure. That’s a very appropriate and very targeted assignment of privileges.

See these resources:

• Fine-tune privilege management
• Tightening security in your PL/SQL code with 12c new features, Part 1
• Tightening security in your PL/SQL code with 12c new features, Part 2

Use static expressions in place of literals

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 National Language Support (NLS) parameter are disallowed.

Expanded and generalized expressions have two primary benefits for PL/SQL developers. First, programs are much more adaptable to changes in their environment. Second, programs are more compact, clearer, and substantially easier to understand and maintain.

Here’s an example:

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;
/

See these resources:

• A roundup of new PL/SQL features in Oracle Database 12c Release 2
• Live SQL code library: Use static expressions

Declare program units as deprecated

Software is constantly evolving. Bugs are fixed; new features are added; and better ways to do things are invented, discovered, and implemented.

A great example of this dynamic is the UTL_CALL_STACK package. This PL/SQL package was first introduced in Oracle Database 12c Release 1, and it improves upon the functionality already provided by the three functions in the DBMS_UTILITY package: FORMAT_CALL_STACK, FORMAT_ERROR_STACK, and FORMAT_ERROR_BACKTRACE.

The same thing happens in PL/SQL code that is developed by customers. Sometimes, the now-outdated subprograms (or other elements) of an old API cannot be removed immediately because it would break existing code. But everyone would like to make sure that all new or refactored code uses the new API.

The new DEPRECATE pragma in Oracle Database 12c Release 2 will help you accomplish this transition in a smooth, error-free fashion. It provides a formal way to communicate information about deprecated elements with a power that ordinary external documentation cannot convey.

Here’s how you can deprecate a subprogram in a package:

CREATE PACKAGE pkg AUTHID DEFINER
AS
  PROCEDURE proc;
  PRAGMA DEPRECATE (
    proc,
    'pkg.proc deprecated. Use pkg.new_proc instead. ');

  PROCEDURE new_proc;
END;

Note the comment added to the pragma. The comment text will be displayed along with the warning or error information.

The following is what happens if anyone uses that deprecated procedure with warnings enabled. Now everyone knows that they shouldn’t use the deprecated procedure.

ALTER SESSION SET plsql_warnings = 'enable:all'
/

CREATE OR REPLACE PROCEDURE use_deprecated
   AUTHID DEFINER
IS
BEGIN
   pkg.proc;
END;
/

Procedure USE_DEPRECATED compiled with warnings

PLW-06020: reference to a deprecated entity: PROC declared in unit PKG[4,14]. pkg.proc deprecated. Use pkg.new_proc instead.

See these resources:

• Document deprecated program units with new pragma
• Live SQL code library: Deprecated

Dig deeper

• See the rest of the PL/SQL series.