Qualified expressions (aka, constructor functions) for collections and records in 18c

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 (it probably never was). But then, either 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: super-smooth 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 that I love the best are the relatively "little" things that make it easy for me to be productive as I churn out the packages (and, yes, I still do write lots of PL/SQL code, most lately 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.

That's a fancy way of saying "constructor function". Or as we say in the documentation:

Through Oracle Database 12c release 2, it was possible to supply the value of certain non-scalar datatype with an expression, by using the type constructor for an object type, nested table or varray.

So if I wanted 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);
BEGIN
   DBMS_OUTPUT.put_line (l_numbers.COUNT);
END;

But if I was using an associative array (aka, 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;

We have had the same problem with populating values of fields in a record:

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 don't have 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 datatype value.

In PL/SQL, we use 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 LiveSQL 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 here, let's go exploring!

Qualified Expressions for Records - Positional Notation

This example uses positional notation to associate values with fields. Notice that I can also use the qualified expression as a default value for my parameter.

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 (l_elephant); 
 
   /* Use the default */ 
   display_species (); 
END;
/

Species: Elephant
Habitat: Savannah
# Left: 10000
Species: Not Set
Habitat: Global
# Left: 0

Qualified Expressions for Records - Named Notation

This example uses named notation to associate values with fields. Notice that I can also use the qualified expression as a default value for my parameter.

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

Qualified Expressions for Arrays 

With associative arrays, you always have to specify the index value (integer or string) with each expression your 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

As should be obvious given the use of named notation, you don't have to specify index values in order - and your array doesn't have to be dense (all index values between lowest and highest defined):

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

Works for string-indexed arrays:

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

The index values do not have to be literals. They can be expressions!

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

And with arrays of records, you can use qualified expressions with both:

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

More? You Want More?

If you still haven't gotten enough of this great feature, check out Tim Hall's ORACLE-BASE article as well. It's the usual top-notch treatment.