[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 13th 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.
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:
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
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:
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
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
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:
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:
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
You can reduce the cost of a context switch to make a function call in SQL in two ways:
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 /
UDF, there is still a context switch, but the runtime cost is reduced.
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:
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:
In earlier releases of Oracle Database, you answered these questions by calling the following
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
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:
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
GRANT create_table_role TO PROCEDURE hr.create_table
To grant from
HR, execute this as
GRANT create_table_role TO hr WITH ADMIN OPTION / ALTER USER hr DEFAULT ROLE ALL EXCEPT create_table_role /
Then execute the grant from
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:
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:
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
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.
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:
Steven Feuerstein was Oracle Corporation's Developer Advocate for PL/SQL between 2014 and 2021. He is an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O'Reilly Media), and currently serving as Senior Advisor for insum Solutions. Steven has been developing software since 1980, spent five years with Oracle back in the "old days" (1987-1992), and was PL/SQL Evangelist for Quest Software (and then Dell) from January 2001 to February 2014 - at which point he returned joyfully to Oracle Corporation. He was one of the original Oracle ACE Directors and writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG's Lifetime Achievement Award (2009).