Easy Initializing for Records and Arrays

Use the new qualified expressions to create functions when and where you need them.

By Steven Feuerstein

May/June 2019

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:

• Qualified Expressions for Records (aka, record constructors)
• Qualified Expressions for Associative Arrays (aka, collection constructors)

But, hey, as long as you’re here, let’s go exploring!

Qualified Expressions for Records, Using Positional Notation

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:

1. To initialize the l_elephant variable
2. As the default value for my species_in parameter

Qualified Expressions for Records, Using Named Notation

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.

Qualified Expressions for Associative Arrays

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

Less—and More-Readable—Code

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!

Next Steps

READ qualified expressions documentation: “Qualified Expressions Overview.”

LEARN more about qualified expressions: “Qualified Expressions in PL/SQL in Oracle Database 18c.”

TRY Oracle Live SQL scripts:
Qualified Expressions for Records (aka, record constructors).
Qualified Expressions for Associative Arrays (aka, collection constructors).

Illustration by Wes Rowell