Qualified expressions improve readability and maintainability of our PL/SQL code. 

While initialising nested tables was quite easy in the past, initialising associative arrays was quite a pain, if it was not done using a query. 

The following examples show how qualified expressions simplify the coding using an associative array, where the key is the name of a city and the content is the population.

Before 18c

DECLARE
   TYPE city_population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
   city_population city_population_type;
BEGIN
   <<Init>>
   BEGIN
      city_population('Zürich')   := 400000;
      city_population('Genf')     := 195000;
      city_population('Basel')    := 175000;
      city_population('Bern')     := 140000;
      city_population('Lausanne') := 135000;
   END Init;
 
   dbms_output.put_line('Population Basel : ' || city_population('Basel'));
END;
/

18c using qualified expression

DECLARE   
   TYPE city_population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(100);   
   city_population city_population_type;
BEGIN   
   <<Init>>   
   BEGIN       
      city_population := city_population_type('Zürich'   => 400000
                                             ,'Genf'     => 195000
                                             ,'Basel'    => 175000
                                             ,'Bern'     => 140000
                                             ,'Lausanne' => 135000);
   END Init;      
  
   dbms_output.put_line('Population Basel : ' || city_population('Basel'));
END;
/

Qualified expression can also be used when working with record structures (and of course also when record structures are part of a associative array).

---

Record structures with qualified expressions

DECLARE
   TYPE emp_type IS RECORD(empno    emp.empno%type
                          ,ename    emp.ename%type
                          ,job      emp.job%type
                          ,mgr      emp.mgr%type 
                          ,hiredate emp.hiredate%type
                          ,sal      emp.sal%type
                          ,comm     emp.comm%type 
                          ,deptno   emp.deptno%type);

   TYPE emp_tab_type IS TABLE OF emp_type INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => emp_type(ename => 'Smith'
                                          , Job => 'Clerk')
                          ,7782 => emp_type(ename => 'Clark'
                                          , Job => 'Manager'
                                          , Sal => 3000)
                          ,7902 => emp_type(ename => 'Ford'
                                          , Sal => 3000
                                          , Deptno => 20));
 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

Now…normally we would make use of %ROWTYPE when we need a record structure that reflects a table row…

DECLARE
   TYPE emp_tab_type IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => ?(ename => 'Smith'
                                   , Job => 'Clerk')
                          ,7782 => ?(ename => 'Clark'
                                   , Job => 'Manager'
                                   , Sal => 3000)
                          ,7902 => ?(ename => 'Ford'
                                   , Sal => 3000
                                   , Deptno => 20)); 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

Hmm….what “constructor” should we use if the associative array holds a rowtype? But…perhaps a subtype could help.

DECLARE
   SUBTYPE emp_row_type IS emp%ROWTYPE;
   TYPE emp_tab_type IS TABLE OF emp_row_type INDEX BY PLS_INTEGER;
   emp_tab emp_tab_type;
BEGIN
   emp_tab := emp_tab_type(7369 => emp_row_type(ename => 'Smith'
                                              , Job => 'Clerk')
                          ,7782 => emp_row_type(ename => 'Clark'
                                              , Job => 'Manager'
                                              , Sal => 3000)
                          ,7902 => emp_row_type(ename => 'Ford'
                                              , Sal => 3000
                                              , Deptno => 20)); 
   dbms_output.put_line(emp_tab(7782).ename);
END;
/

ORA-06550: line 6, column 36: 
PLS-00222: no function with name 'EMP_ROW_TYPE' exists in this scope

Too bad…that would have been nice too.

---

Conclusion

Qualified expression will help us to write code that is more readable and easier. It’s a nice new feature that I will use for sure.

And perhaps, someday, the %ROWTYPE thing will be possible too.