As anyone who has followed me over the years knows, I like the Oracle PL/SQL language. Sure, it’s not the newest, coolest kid on the block (and it probably never was). But then, neither am I. PL/SQL is, on the other hand, a delightfully straightforward, easy-to-learn and -write language that serves its purpose well: implement APIs to data (SQL) and business logic, right inside the database.
To serve that purpose, of course, PL/SQL needs to support lots of “big ticket” functionality: supersmooth and easy native dynamic SQL, canonicalization of static SQL to minimize the need for hard-parsing, invoker rights (AUTHID CURRENT_USER), and so much more.
But I must confess: The features of PL/SQL I love the most are the relatively little things that make it easy for me to be productive as I churn out packages (and, yes, I still do write lots of PL/SQL code, most recently for the Oracle Dev Gym, an active-learning website featuring quizzes, workouts, and classes).
And that’s why my favorite PL/SQL enhancement in Oracle Database 18c is the qualified expression. If you are not familiar with that term, then how about “constructor function”?
Ever since Oracle Database introduced the object-relational model, in version 8, PL/SQL developers 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;
So 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, then the collection will be initialized but will be empty.
But before Oracle Database 18c, if I was using an associative array (aka, an index-by table), this 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 Release 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.
A qualified expression combines expression elements to create values of a RECORD type or associative array type (both integer- and string-indexed). Qualified expressions use an explicit type indication to provide the type of the qualified item. This explicit indication is known as a typemark.
I’ve put together a couple of Oracle Live SQL scripts to make it easy for you to play around with this great feature:
But, hey, as long as you’re here, let’s go exploring!
This example uses positional notation to associate values with fields. This means that each expression in the list is assigned to the field in the same position (for example, Savannah is assigned to habitat_type).
DECLARE TYPE species_rt IS RECORD ( species_name VARCHAR2 (100), habitat_type VARCHAR2 (100), surviving_population INTEGER); l_elephant species_rt := species_rt ('Elephant', 'Savannah', '10000'); PROCEDURE display_species ( species_in species_rt DEFAULT species_rt ('Not Set', 'Global', 0)) IS BEGIN DBMS_OUTPUT.put_line ('Species: ' || species_in.species_name); DBMS_OUTPUT.put_line ('Habitat: ' || species_in.habitat_type); DBMS_OUTPUT.put_line ('# Left: ' || species_in.surviving_population); END; BEGIN display_species (species_in => l_elephant); /* Use the default */ display_species (); END; / Species: Elephant Habitat: Savannah # Left: 10000 Species: Not Set Habitat: Global # Left: 0
Note that in this example, I used the qualified expression for the record type—species_rt—in two places:
Named notation has long been a feature of parameter lists in PL/SQL: You use the => operator to associate an argument value with its formal parameter. See the first call to display_species in the previous code block for an example.
You can now also use named notation in a record’s qualified expression. In the block below, I provide both the names of the fields and their values.
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; / Species: Elephant
I suggest always using named notation in your qualified expressions. This enables you to change the order of the individual assignments, which might be useful for emphasizing key values but also to improve the overall readability of your code.
When you invoke a qualified expression for a record, you can choose between named and positional notation. With associative arrays, you must always specify the index value (integer or string) for each expression you want to stuff into the array, as in
DECLARE TYPE ints_t IS TABLE OF INTEGER INDEX BY PLS_INTEGER; l_ints ints_t := ints_t (1 => 55, 2 => 555, 3 => 5555); BEGIN FOR indx IN 1 .. l_ints.COUNT LOOP DBMS_OUTPUT.put_line (l_ints (indx)); END LOOP; END; / 55 555 5555
And just as you can change the order of a record’s fields in the qualified expression when you use named notation, you don’t need to specify index values in order with an array’s qualified expression, as you can see here:
DECLARE TYPE ints_t IS TABLE OF INTEGER INDEX BY PLS_INTEGER; l_ints ints_t := ints_t (2 => 55, 1 => 555, 3 => 5555); BEGIN FOR indx IN 1 .. l_ints.COUNT LOOP DBMS_OUTPUT.put_line (l_ints (indx)); END LOOP; END; / 55 555 5555
In case you’re wondering, you cannot use named notation with the constructor function of a nested table or varray. If you try to do so, the PL/SQL compiler will reject it with a PLS-00306: wrong number or types of arguments in call error.
That’s not too surprising, since nested tables and varrays are always populated densely from index value 1. This means that there are no undefined gaps between the lowest and the highest defined index values. (This is an initial requirement; you can introduce a gap between values in a nested table later, using the DELETE method).
Values in associative arrays, on the other hand, can be dense or sparse (with at least one undefined index value between the lowest and the highest). This is especially and obviously the case for string-indexed associative arrays (nested tables and varrays support only integer indexes).
So with qualified expressions for associative arrays, you can specify whatever index values you’d like. For example:
DECLARE TYPE ints_t IS TABLE OF INTEGER INDEX BY PLS_INTEGER; l_ints ints_t := ints_t (600 => 55, -5 => 555, 200000 => 5555); l_index pls_integer := l_ints.first; BEGIN WHILE l_index IS NOT NULL LOOP DBMS_OUTPUT.put_line (l_index || ' => ' || l_ints (l_index)); l_index := l_ints.NEXT (l_index); END LOOP; END; / -5 => 555 600 => 55 200000 => 5555
And with string-indexed arrays, “the dictionary’s the limit.” You can use any string you’d like as an index value.
DECLARE TYPE by_string_t IS TABLE OF INTEGER INDEX BY VARCHAR2(100); l_stuff by_string_t := by_string_t ('Steven' => 55, 'Loey' => 555, 'Juna' => 5555); l_index varchar2(100) := l_stuff.first; BEGIN DBMS_OUTPUT.put_line (l_stuff.count); WHILE l_index IS NOT NULL LOOP DBMS_OUTPUT.put_line (l_index || ' => ' || l_stuff (l_index)); l_index := l_stuff.NEXT (l_index); END LOOP; END; / 3 Juna => 5555 Loey => 555 Steven => 55
Furthermore, those index values do not need to be literals. They can be expressions; they simply must resolve explicitly or implicitly to the correct type (integer or string).
DECLARE TYPE by_string_t IS TABLE OF INTEGER INDEX BY VARCHAR2 (100); l_stuff by_string_t := by_string_t (UPPER ('Grandpa Steven') => 55, 'Loey'||'Juna' => 555, SUBSTR ('Happy Family', 7) => 5555); l_index varchar2(100) := l_stuff.first; BEGIN DBMS_OUTPUT.put_line (l_stuff.count); WHILE l_index IS NOT NULL LOOP DBMS_OUTPUT.put_line (l_index || ' => ' || l_stuff (l_index)); l_index := l_stuff.NEXT (l_index); END LOOP; END; / 3 Family => 5555 GRANDPA STEVEN => 55 LoeyJuna => 555
Finally, there’s nothing stopping you from using qualified expressions for arrays as well as records, which comes in really handy when you have a collection of records!
DECLARE TYPE species_rt IS RECORD ( species_name VARCHAR2 (100), habitat_type VARCHAR2 (100), surviving_population INTEGER ); TYPE species_t IS TABLE OF species_rt INDEX BY PLS_INTEGER; l_species species_t := species_t ( 2 => species_rt ('Elephant', 'Savannah', '10000'), 1 => species_rt ('Dodos', 'Mauritius', '0'), 3 => species_rt ('Venus Flytrap', 'North Carolina', '250')); BEGIN FOR indx IN 1 .. l_species.COUNT LOOP DBMS_OUTPUT.put_line (l_species (indx).species_name); END LOOP; END; / Dodos Elephant Venus Flytrap
PL/SQL has always been an incredibly readable language. It relies heavily on common English language keywords. When you make use of all the modular features of PL/SQL, it is quite possible to produce a block of code that can almost literally be read like a book.
Certainly, prior to Oracle Database 18c, you could write and then read all those inline assignments. You could also write your own custom functions—one for each distinct record and array type—and use them in place of inline assignments.
Now, with Oracle Database 18c, you can write even less code and end up with a code block that is even more readable. Let Oracle Database do even more of the heavy lifting: Use qualified expressions for your records and arrays, and focus even more of your time on implementing business logic right there in the database!
READ qualified expressions documentation: “Qualified Expressions Overview.”
LEARN more about qualified expressions: “Qualified Expressions in PL/SQL in Oracle Database 18c.”
Illustration by Wes Rowell
Steven Feuerstein is Oracle Corporation's Developer Advocate for PL/SQL, and 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). 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).