Skip to main content

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.

Comments

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel