Qualified expressions for PL/SQL were introduced in version 18 of the Oracle database. They provide a clearer way of working with collections. At the bottom of this post is the link to the official documentation.
However recently I encountered something that left me puzzled and I still can't explain the reason why. So this blogpost is just about my observations and I welcome any comments.
First a short demo of how you can work with Qualified Expressions.
SQL> select banner 2 from v$version; BANNER ___________________________________________________________________ Oracle Database 18c EE High Perf Release 184.108.40.206.0 - Production
SQL> set serveroutput on SQL> declare 2 type rec_t is record 3 (num1 number 4 ,num2 number); 5 type tbl_t is table of rec_t 6 index by pls_integer; 7 l_tbl tbl_t; 8 l_idx pls_integer; 9 begin 10 for i in 1..3 11 loop 12 l_tbl := tbl_t (i => rec_t (num1 => i 13 ,num2 => i)); 14 end loop; 15 -- 16 l_idx := l_tbl.first; 17 while l_idx is not null 18 loop 19 sys.dbms_output.put_line (to_char (l_tbl(l_idx).num1) 20 ||' - '|| 21 to_char (l_tbl(l_idx).num2) 22 ); 23 l_idx := l_tbl.next (l_idx); 24 end loop; 25 end; 26 /On line 2 - 4 a Record Type is declared with two numeric attributes. Line 5 and 6 define an Associative Array of the Record Type.
The Qualified Expression is on lines 12 and 13.
In the loop we assign the loop index variable to the Record Type using the Qualified Expression syntax.
To view the content of the Associative Array, lines 16 through 24 are used. When running this block of code the output is:
1 - 1 2 - 2 3 - 3
Cursor For Loop
The previous example used a Numeric For Loop, but I observed strange things when using a Cursor For Loop.
SQL> drop table t purge 2 / Table T dropped. SQL> create table t 2 (id number 3 ,yes_no varchar2(1) 4 ) 5 / Table T created. SQL> insert into t values (1, 'Y'); 1 row inserted. SQL> insert into t values (2, 'N'); 1 row inserted. SQL> insert into t values (3, 'Y'); 1 row inserted. SQL> commit; Commit complete.The table contains three records with different Yes/No-indicators. Instead of showing the complete block of code I will only show the changes that I made using the previous example.
10 for r in (select * from t) 11 loop 12 l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id 13 ,num2 => r.id)); 14 end loop;The Numeric For Loop is removed and a Cursor For Loop is inserted in the code block. Instead of using the index-variable from the first code example, I am using the current count of the Associative Array and add one to that (l_tbl.count + 1).
The results are the same as the first code block.
1 - 1 2 - 2 3 - 3
...but you said Strange Things?
Yes, I did... but so far, it seems normal.
The strange thing happens when a condition is added inside the For-Loop.
10 for r in (select * from t) 11 loop 12 if r.yes_no = 'Y' 13 then 14 l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id 15 ,num2 => r.id)); 16 else 17 l_tbl := tbl_t (l_tbl.count + 1 => rec_t (num1 => r.id 18 ,num2 => r.id)); 19 20 end if; 21 end loop;The table has a Yes_No column and inside the For Loop, the value of this column is used.
When you examine the code above, you will notice that it will assign a value to the collection regardless of the value of the column. The assignment is exactly the same in lines 14-15 and 17-18. The content of the Associative Array after the loop should be the same as before, but this is not the case.
The result looks like this:
1 - 1 3 - 3What happened to the second record?... Your guess is as good as mine.
I've tried several variations, like using a local counter to assign instead of the collection.count + 1, but that doesn't make a difference.
Like said before: I welcome your comments for more insight into this strange behavior.
The same is reproducable on Oracle 19...
SQL> select banner 2 from v$version 3 / BANNER _________________________________________________________________________ Oracle Database 19c Enterprise Edition Release 220.127.116.11.0 - Production