Qualified Expressions for Records (aka, record constructors)

View All Scripts Login to Run Script

Script Name Qualified Expressions for Records (aka, record constructors)
Description Aggregates and their necessary adjunct, qualified expressions, improve program clarity and programmer productivity. Through Oracle Database 12c release 2, it was possible to supply the value of a non-scalar datatype with an expression, for example by using the type constructor for a nested table. Starting with Oracle Database Release 18c, any PL/SQL value can be provided by an expression (for example for a record or for an associative array) 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. Qualified expressions use an explicit type indication to provide the type of the qualified item. This explicit indication is known as a typemark.
Area PL/SQL General
Contributor Steven Feuerstein
Created Wednesday February 14, 2018

Statement 1

Ah, the feature we've all been waiting for! You no longer need to write cumbersome, field-by-field assignments to populate a record - in the executable section. Now you can "call a function" (qualified expression) with the same name as your record type and pass values for your fields inside it. This example uses positional notation to associate values with fields. Notice also that I use the qualified expression as a default value for my parameter!

Initialize Record in Declaration Section!

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

Statement 2

create table species 
   (
      species_name           VARCHAR2 (100),
      habitat_type           VARCHAR2 (100),
      surviving_population   INTEGER
)

Table created.

Statement 3

This feature is only available for user-defined types.

Sorry, Not For %ROWTYPE Record Types!

DECLARE 
   l_elephant   species%rowtype := species%rowtype ('Elephant', 'Savannah', '10000'); 
BEGIN 
   NULL; 
END;

ORA-06550: line 2, column 36:
PLS-00431: bulk SQL attributes must use a single index

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 4

Just like when you specify actual arguments for your formal parameters, you can use named notation to specify the field to which the expression should be assigned. This also means you can switch up the order in which you provide expressions for fields.

Use Named Notation in Qualified Expression

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'); 
 
   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

Statement 5

Sure, species1_rt and species2_rt are "the same" - to our eyeballs. But to the compiler they are different types.

Types Must Match!

DECLARE 
   TYPE species1_rt IS RECORD 
   ( 
      species_name           VARCHAR2 (100), 
      habitat_type           VARCHAR2 (100), 
      surviving_population   INTEGER 
   ); 
 
   TYPE species2_rt IS RECORD 
   ( 
      species_name           VARCHAR2 (100), 
      habitat_type           VARCHAR2 (100), 
      surviving_population   INTEGER 
   ); 
 
   l_species1   species1_rt := species1_rt ('Elephant', 'Savannah', '10000'); 
 
   l_species2   species2_rt; 
BEGIN 
   l_species2 := l_species1; 
END;

ORA-06550: line 20, column 18:
PLS-00382: expression is of wrong type

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 6

So if you've got two different types that are "the same", you have to fall back on a field-by-field transfer of values.

Field by Field for Different "Same" Types

DECLARE 
   TYPE species1_rt IS RECORD 
   ( 
      species_name           VARCHAR2 (100), 
      habitat_type           VARCHAR2 (100), 
      surviving_population   INTEGER 
   ); 
 
   TYPE species2_rt IS RECORD 
   ( 
      species_name           VARCHAR2 (100), 
      habitat_type           VARCHAR2 (100), 
      surviving_population   INTEGER 
   ); 
 
   l_species1   species1_rt := species1_rt ('Elephant', 'Savannah', '10000'); 
 
   l_species2   species2_rt; 
BEGIN 
   l_species2 := 
      species2_rt (l_species1.species_name, 
                   l_species1.habitat_type, 
                   l_species1.surviving_population); 
END;

Statement processed.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database