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 18.0.0.0.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
No surprises here, it works as expected. Well, actually it is surprising take a look at
the follow-up blog.
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 - 3
What 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 19.0.0.0.0 - Production
Links
