qualified-expressions-in-plsql-18c

Qualified Expressions in PL/SQL in Oracle Database 18c

Qualified expressions provide and alternative way to define the value
of complex objects, which in some cases can make the code look neater.
* [46]Syntax
* [47]Qualified Expressions with Record Types
* [48]Qualified Expressions with Associative Arrays

Related articles.
* [49]PL/SQL New Features and Enhancements in Oracle Database 12c
Release 1 (12.1)
* [50]Oracle SQL and PL/SQL Enhancements in Oracle Database 12c
Release 2 (12.2)

Syntax

The basic syntax for a qualified expression is as follows
typemark(aggregate)

The typemark is the type name. The aggregate is the data associated
with this instance of the type. The data can specified using positional
or the named association syntax. That all sounds a bit complicated, but
it's similar to using a constructor for a object and will be obvious
once you see some examples.

Qualified Expressions with Record Types

Records with large numbers of columns can be a little clumsy to work
with. Qualified expressions can simplify code in some circumstances.

The following example shows three ways to populate a record in Oracle
18c. The first method, available in previous releases, involves a
direct assignment to each column in the record variable. The second
method uses a qualified expression where the aggregate uses positional
notation. The third example uses a qualified expression where the
aggregate uses the named association syntax.
DECLARE
TYPE t_rec IS RECORD (
id NUMBER,
val1 VARCHAR2(10),
val2 VARCHAR2(10),
val3 VARCHAR2(10),
val4 VARCHAR2(10),
val5 VARCHAR2(10),
val6 VARCHAR2(10),
val7 VARCHAR2(10),
val8 VARCHAR2(10),
val9 VARCHAR2(10)
);

l_rec t_rec;
BEGIN
-- Pre-18c - Direct assignment to record columns.
l_rec.id := 1;
l_rec.val1 := 'ONE';
l_rec.val2 := 'TWO';
l_rec.val3 := 'THREE';
l_rec.val4 := 'FOUR';
l_rec.val5 := 'FIVE';
l_rec.val6 := 'SIX';
l_rec.val7 := 'SEVEN';
l_rec.val8 := 'EIGHT';
l_rec.val9 := 'NINE';

-- 18c - Qualified expression using position notation.
l_rec := t_rec(1, 'ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX', 'SEVEN', 'EIGH
T', 'NINE');

-- 18c - Qualified expression using named association.
l_rec := t_rec(id => 1,
val1 => 'ONE',
val2 => 'TWO',
val3 => 'THREE',
val4 => 'FOUR',
val5 => 'FIVE',
val6 => 'SIX',
val7 => 'SEVEN',
val8 => 'EIGHT',
val9 => 'NINE');
END;
/

The first and last examples show clearly which columns gets which
values, but they take a bit more space. The qualified expression using
position notation is more compact, but relies on you knowing the order
of the columns. In this case it's easy as the type if declared directly
above. It would be less obvious if the type were defines in a package
specification.

Things look a little different if we are only dealing with a subset of
the columns. In the following example the qualified expression using
named association looks neater, but still similar to the direct
assignment to the record columns.
DECLARE
TYPE t_rec IS RECORD (
id NUMBER,
val1 VARCHAR2(10),
val2 VARCHAR2(10),
val3 VARCHAR2(10),
val4 VARCHAR2(10),
val5 VARCHAR2(10),
val6 VARCHAR2(10),
val7 VARCHAR2(10),
val8 VARCHAR2(10),
val9 VARCHAR2(10)
);

l_rec t_rec;
BEGIN
-- Pre-18c - Direct assignment to record columns.
l_rec.id := 1;
l_rec.val1 := 'ONE';
l_rec.val9 := 'NINE';

-- 18c - Qualified expression using position notation.
l_rec := t_rec(1, 'ONE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'NINE');

-- 18c - Qualified expression using named association.
l_rec := t_rec(id => 1, val1 => 'ONE', val9 => 'NINE');
END;
/

The difference becomes more apparent when the same variable is used for
multiple sparse records, each referencing different columns in the
record. In the following example the same record variable is used twice
for each method. In the first pass the val1 and val9 columns are set.
In the second pass the val2 and val8 columns are set. After each
assignment the values of the val1 and val9 columns are displayed. The
qualified expressions represent a new instance of the record, so all
the unused columns are blanked explicitly or implicitly. Without the
qualified expression it is up to the developer to blank the previous
values manually.
SET SERVEROUTPUT ON
DECLARE
TYPE t_rec IS RECORD (
id NUMBER,
val1 VARCHAR2(10),
val2 VARCHAR2(10),
val3 VARCHAR2(10),
val4 VARCHAR2(10),
val5 VARCHAR2(10),
val6 VARCHAR2(10),
val7 VARCHAR2(10),
val8 VARCHAR2(10),
val9 VARCHAR2(10)
);

l_rec t_rec;
BEGIN
-- Pre-18c - Direct assignment to record columns.
l_rec.id := 1;
l_rec.val1 := 'ONE';
l_rec.val9 := 'NINE';
DBMS_OUTPUT.put_line('(1) Record1 val1 = ' || l_rec.val1 || ' val9 = ' || l_r
ec.val9);

l_rec.id := 2;
l_rec.val2 := 'TWO';
l_rec.val8 := 'EIGHT';
DBMS_OUTPUT.put_line('(1) Record2 val1 = ' || l_rec.val1 || ' val9 = ' || l_r
ec.val9);

-- 18c - Qualified expression using position notation.
l_rec := t_rec(1, 'ONE', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'NINE');
DBMS_OUTPUT.put_line('(2) Record1 val1 = ' || l_rec.val1 || ' val9 = ' || l_r
ec.val9);

l_rec := t_rec(1, NULL, 'TWO', NULL, NULL, NULL, NULL, NULL, 'EIGHT', NULL);
DBMS_OUTPUT.put_line('(2) Record2 val1 = ' || l_rec.val1 || ' val9 = ' || l_r
ec.val9);

-- 18c - Qualified expression using named association.
l_rec := t_rec(id => 1, val1 => 'ONE', val9 => 'NINE');
DBMS_OUTPUT.put_line('(3) Record1 val1 = ' || l_rec.val1 || ' val9 = ' || l_r
ec.val9);

l_rec := t_rec(id => 1, val2 => 'TWO', val8 => 'EIGHT');
DBMS_OUTPUT.put_line('(3) Record2 val1 = ' || l_rec.val1 || ' val9 = ' || l_r
ec.val9);
END;
/
(1) Record1 val1 = ONE val9 = NINE
(1) Record2 val1 = ONE val9 = NINE
(2) Record1 val1 = ONE val9 = NINE
(2) Record2 val1 = val9 =
(3) Record1 val1 = ONE val9 = NINE
(3) Record2 val1 = val9 =


PL/SQL procedure successfully completed.

SQL>

We can even use a qualified expression in the definition of a default
value. In the following example a procedure accepts a record type as a
parameter, which has a default value specified using a qualified
expression.
DECLARE
TYPE t_rec IS RECORD (
id NUMBER,
val1 VARCHAR2(10),
val2 VARCHAR2(10)
);

PROCEDURE dummy (p_rec IN t_rec DEFAULT t_rec(id => 1, val1 => 'ONE')) AS
BEGIN
NULL;
END;
BEGIN
NULL;
END;
/

Qualified Expressions with Associative Arrays

When dealing with associative arrays we have the option of assigning
values to the individual elements of the associative array, or creating
a new associative array using a qualified expression. The following
example uses a PLS_INTEGER as the index of the associative array.
DECLARE
TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;

l_tab t_tab;
BEGIN
-- Pre-18c - Direct assignment to elements of the collection.
l_tab(1) := 'ONE';
l_tab(2) := 'TWO';
l_tab(3) := 'THREE';

-- 18c - Qualified expression using named association.
l_tab := t_tab(1 => 'ONE',
2 => 'TWO',
3 => 'THREE');
END;
/

This example uses a VARCHAR2 as the index of the associative array.
DECLARE
TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);

l_tab t_tab;
BEGIN
-- Pre-18c - Direct assignment to record columns.
l_tab('IND1') := 'ONE';
l_tab('IND2') := 'TWO';
l_tab('IND3') := 'THREE';

-- 18c - Qualified expression using named association.
l_tab := t_tab('IND1' => 'ONE',
'IND2' => 'TWO',
'IND3' => 'THREE');
END;
/

Remember, the qualified expression creates a new instance of the
associative array, so any previously defined elements are removed. In
this example we create an associative array with three elements, then
immediately assign a two element associative array. If we try to
reference the element with index 2 we get a NO_DATA_FOUND exception.
SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);

l_tab t_tab;
BEGIN
-- 18c - Qualified expression using named association.
l_tab := t_tab(1 => 'ONE',
2 => 'TWO',
3 => 'THREE');

l_tab := t_tab(1 => 'ONE',
3 => 'THREE');

DBMS_OUTPUT.put_line('2=' || l_tab(2));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('I knew this would cause a NDF error!');
END;
/
I knew this would cause a NDF error!

PL/SQL procedure successfully completed.

SQL>

In the following example a procedure accepts an associative array as a
parameter, which has a default value specified using a qualified
expression.
DECLARE
TYPE t_tab IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);

PROCEDURE dummy (p_tab IN t_tab DEFAULT t_tab(1 => 'ONE', 2 => 'TWO',3 => 'THR
EE')) AS
BEGIN
NULL;
END;
BEGIN
NULL;
END;
/

For more information see:
* [51]Qualified Expressions Overview
--------------------------
Source .... : https://oracle-base.com/articles/18c/qualified-expressions-in-plsql-18c
--------------------------