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

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Table Functions, Part 1: Introduction and Exploration

August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latte…

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 perspective…