Overview
This document talks about how Qualified Expressions for Associative Array works before and after the oracle 18c version.
Technologies and Tools Used
The following technologies has been used to achieve the expected output.
- Oracle PLSQL
Use Case
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.
Before 18c version Associate arrays will not allow to initiate the values in the declaration part, only we have to be initialize the values inside main block. But now we can initialize a value in the declaration part itself. We see some examples below.
Architecture
We can see few examples below like how Qualified Expressions for Associative Array works before 12 c and how it works after the 18C version.
Before 18C
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;
Error Report:
PL/SQL: Item ignored ORA-06550: line 4, column 5: PLS-00320: the declaration of the type of this expression is incomplete or malformed ORA-06550: line 4, column 5: PL/SQL: Statement ignored
In the above code, type ints_t is created with index value by pls_integer and tried to initialize the value in the declaration section with named notation. When we compile the code it gives an error that declaration of the type of this expression is incomplete. To overcome this issues from 18c onwards, oracle supports to initialize values in the declaration part of an associative array.
After 18C Onwards
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;
Output:
55
555
5555
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;
Output:
-5 => 555
600 => 55
200000 => 5555
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;
Output:
Dodos
Elephant
Venus Flytrap
Exact Same Type Must Be Used
DECLARE
TYPE species_rt IS RECORD
(
species_name VARCHAR2 (100),
habitat_type VARCHAR2 (100),
surviving_population INTEGER
);
TYPE species_t1 IS TABLE OF species_rt
INDEX BY PLS_INTEGER;
TYPE species_t2 IS TABLE OF species_rt
INDEX BY PLS_INTEGER;
l_species species_t1 :=
species_t2 (
1 => species_rt (‘Elephant’, ‘Savannah’, ‘10000’));
BEGIN
NULL;
END;
Error Report:
ORA-06550: line 16, column 7: PLS-00382: expression is of wrong type
Qualified Expressions 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;
Output:
Juna => 5555
Loey => 555
Steven => 55
Conclusion
The above examples shows the data can specified using positional or the named association syntax. 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.
