DBA Hub

📋Steps in this guide1/8

Qualified Expressions Enhancements in Oracle Database 21c

Oracle database 21c makes it even easier to populate collections using qualified expressions.

oracle 21cconfigurationintermediate
by OracleDba
13 views
1

Before We Begin

This article makes some assumptions about prior knowledge.
2

Setup

Some of the examples in this article use the following objects. The query below displays the data in the table. All of the examples are being run in SQLcl and assume you have the server output enabled.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
create or replace function num_to_word (p_num number) return varchar2 is
begin
  return upper(to_char(to_date(p_num, 'j'), 'jsp'));
end;
/


-- drop table t1 purge;

create table t1 as
select level+10 as id,
       num_to_word(level+10) as description,
       trunc(sysdate) as created_date
from   dual
connect by level <= 5;

column description format a20
column created_date format a12

select id, description, created_date from t1;

        ID DESCRIPTION          CREATED_DATE
---------- -------------------- ------------
        11 ELEVEN               02-JAN-21
        12 TWELVE               02-JAN-21
        13 THIRTEEN             02-JAN-21
        14 FOURTEEN             02-JAN-21
        15 FIFTEEN              02-JAN-21

SQL>

SQL> set serveroutput on
3

Positional Notation for Associative Array

From 18c onward we've been able to populate an associative array (index-by table) using a qualified expression and named association, but 21c now allows us to populate associative arrays using positional notation, similar to how we can populate nested tables and varrays. When populating an associative array using positional notation, the index is an integer from 1 to N, based on the order of the elements specified.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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');

  -- 21c - Qualified expression using positional notation.
  l_tab := t_tab('ONE', 'TWO', 'THREE');
end;
/
4

Basic Iterator

The basic iterator populates the collection as follows. - index : The value of the iterand. - value : The value of the expression to the right of "=>". The expression can reference the iterand value. In this example we populate the collection with indexes 1 to 5, and values of i+10. The expression can evaluate to a different type. In the following example we use a collection of , and populate it with the word equivalent of the iterand. We can use most of the new features of the introduced in Oracle database 21c. The following example includes stepped iteration, skipping and stopping. It's a rather silly example, but it demonstrates their inclusion.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
declare
  type tab_t is table of pls_integer index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 5 => i+10
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  value=' || v);
  end loop;
end;
/
index=1  value=11
index=2  value=12
index=3  value=13
index=4  value=14
index=5  value=15

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of varchar2(50) index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 5 => num_to_word(i)
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  value=' || v);
  end loop;
end;
/
index=1  value=ONE
index=2  value=TWO
index=3  value=THREE
index=4  value=FOUR
index=5  value=FIVE

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of varchar2(50) index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 50 by 2 while i < 20 when i > 10 => num_to_word(i)
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  value=' || v);
  end loop;
end;
/
index=11  value=ELEVEN
index=13  value=THIRTEEN
index=15  value=FIFTEEN
index=17  value=SEVENTEEN
index=19  value=NINETEEN

PL/SQL procedure successfully completed.

SQL>
5

Index Iterator

The index iterator populates the collection as follows. - index : The value of the expression to the left of "=>". The expression can reference the iterand value. - value : The value of the expression to the right of "=>". The expression can reference the iterand value. In this example we populate the collection with indexes 10 to 50, and values 100 to 500. The expressions can evaluate to different types. In the following example we use a collection of indexed by . We can use most of the new features of the introduced in Oracle database 21c. The following example includes stepped iteration, skipping and stopping.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
declare
  type tab_t is table of pls_integer index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 5 index i*10 => i*100
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  value=' || v);
  end loop;
end;
/
index=10  value=100
index=20  value=200
index=30  value=300
index=40  value=400
index=50  value=500

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of date index by varchar2(50);
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 5 index num_to_word(i) => sysdate+i
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || rpad(i,5,' ') || '  value=' || v);
  end loop;
end;
/
index=FIVE   value=07-JAN-21
index=FOUR   value=06-JAN-21
index=ONE    value=03-JAN-21
index=THREE  value=05-JAN-21
index=TWO    value=04-JAN-21

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of date index by varchar2(50);
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 50 by 2 while i < 20 when i > 10 index num_to_word(i) => sysdate+i
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || rpad(i,9,' ') || '  value=' || v);
  end loop;
end;
/
index=ELEVEN     value=13-JAN-21
index=FIFTEEN    value=17-JAN-21
index=NINETEEN   value=21-JAN-21
index=SEVENTEEN  value=19-JAN-21
index=THIRTEEN   value=15-JAN-21

PL/SQL procedure successfully completed.

SQL>
6

Sequence Iterator

The sequence iterator populates the collection as follows. - index : The value 1 to N. It does not relate to the iterand. - value : The value of the expression to the right of "=>". The expression can reference the iterand value. In this example we populate the collection with indexes 1 to 5, even though the iterand is 11 to 15, and values of the i+10. The expression can evaluate to a different type. In the following example we use a collection of , and populate it with the word equivalent of the iterand. Once again, the index is not related to the iterand. We can use most of the new features of the introduced in Oracle database 21c. The following example includes stepped iteration, skipping and stopping. Remember, the iteration control applies to the value of the iterand, not the index.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
declare
  type tab_t is table of pls_integer index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 11 .. 15 sequence => i+10
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  value=' || v);
  end loop;
end;
/
index=1  value=21
index=2  value=22
index=3  value=23
index=4  value=24
index=5  value=25

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of varchar2(50) index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 11 .. 15 sequence => num_to_word(i)
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  value=' || v);
  end loop;
end;
/
index=1  value=ELEVEN
index=2  value=TWELVE
index=3  value=THIRTEEN
index=4  value=FOURTEEN
index=5  value=FIFTEEN

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of varchar2(50) index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 50 by 2 while i < 20 when i > 10 sequence => num_to_word(i)
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  value=' || v);
  end loop;
end;
/
index=1  value=ELEVEN
index=2  value=THIRTEEN
index=3  value=FIFTEEN
index=4  value=SEVENTEEN
index=5  value=NINETEEN

PL/SQL procedure successfully completed.

SQL>
7

Nested Tables and Varrays

The examples so far have focused on associative arrays (index-by tables), but the index and sequence iterator syntax can also be used for nested tables and varrays. The example below shows both iterators with nested tables. Attempting to use a basic iterator always results in an error. The index iterator does something odd if we don't start from 1, or if we alter the expression on the left side of "=>". It generates empty collection elements. I'm not sure if this is intentional, or a bug.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
declare
  type tab_t is table of varchar2(50);
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 5 sequence => num_to_word(i)
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  value=' || v);
  end loop;
end;
/
index=1  value=ONE
index=2  value=TWO
index=3  value=THREE
index=4  value=FOUR
index=5  value=FIVE

PL/SQL procedure successfully completed.

SQL>


declare
  type tab_t is table of varchar2(50);
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 5 index i => num_to_word(i)
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  value=' || v);
  end loop;
end;
/
index=1  value=ONE
index=2  value=TWO
index=3  value=THREE
index=4  value=FOUR
index=5  value=FIVE

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of varchar2(50);
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 5 => num_to_word(i)
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  value=' || v);
  end loop;
end;
/
Error report -
ORA-06550: line 6, column 18:
PLS-00868: The iterand type for an iteration control is not compatible with the collection index type, use SEQUENCE, or INDEX iterator association instead of a basic iterator association.

declare
  type tab_t is table of varchar2(50);
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 6 .. 10 index i => num_to_word(i)
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || rpad(i,2,' ') || '  value=' || v);
  end loop;
end;
/
index=1   value=
index=2   value=
index=3   value=
index=4   value=
index=5   value=
index=6   value=SIX
index=7   value=SEVEN
index=8   value=EIGHT
index=9   value=NINE
index=10  value=TEN

PL/SQL procedure successfully completed.

SQL>


declare
  type tab_t is table of varchar2(50);
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in 1 .. 5 index i+5 => num_to_word(i)
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || rpad(i,2,' ') || '  value=' || v);
  end loop;
end;
/
index=1   value=
index=2   value=
index=3   value=
index=4   value=
index=5   value=
index=6   value=ONE
index=7   value=TWO
index=8   value=THREE
index=9   value=FOUR
index=10  value=FIVE

PL/SQL procedure successfully completed.

SQL>
8

Populating Collections From Database Tables

There are a number of existing ways to populate a collection from a database table. Probably the most efficient way is to use a bulk bind, which works well with associative arrays, nested table and varrays. The following example uses to build an associative array from the contents of the table. Unfortunately, the index of the associative array is just a sequence from 1 to N, rather than matching the primary key column. You can read about the performance benefits of bulk binds here . If we wanted to populate an associative array from the table and make the array index match the primary key column, we would typically have to do something like the following. We define an associative array (index-by table) type (tab_t) based on the row type. We create a variable (l_tab) based on the table type. We use a cursor to retrieve the rows and use them to populate the collection, using the value for the collection index for convenience. We can then display the contents of the collection. With the enhancements to the qualified expressions, we can populate a collection in a single step. We define the table type in the same way, but we can populate the collection from the query directly using the table type constructor and an iterator based on a query. We use the iterator to indicate we want the collection index to be based on the column from the query. The operand matches the rowtype of the query. The fact we are using an iterator means we have access to the skipping and stopping functionality of the iterator if we need it. The following example uses the clause to only match rows with an column value that is even. Of course, you could argue this would be better done in the query itself. If we didn't care about the collection index matching the column from the query, we could just use the iterator to let it use a default sequence. In the previous examples we used an implicit cursor. The iterators work equally well with an explicit cursor, as demonstrated below. We can also use a type. There are two things to notice here. First, we have to open and close the ref cursor ourselves. Second, we've had to explicitly type the operand, as we've used a weakly typed ref cursor. The documentation suggests it should also be possible to use dynamic SQL using the command, but that seems to give errors when I try. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
declare
  type tab_t is table of t1%rowtype index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  select id, description, created_date
  bulk collect into l_tab
  from t1;

  -- Display the contents of the collection.
  for i in 1 .. l_tab.count loop
    dbms_output.put_line('index=' || i || '  id=' || l_tab(i).id ||
                         '  description=' || rpad(l_tab(i).description,8,' ') ||
                         '  created_date=' || l_tab(i).created_date);
  end loop;
end;
/
index=1  id=11  description=ELEVEN    created_date=02-JAN-21
index=2  id=12  description=TWELVE    created_date=02-JAN-21
index=3  id=13  description=THIRTEEN  created_date=02-JAN-21
index=4  id=14  description=FOURTEEN  created_date=02-JAN-21
index=5  id=15  description=FIFTEEN   created_date=02-JAN-21

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of t1%rowtype index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  for cur_rec in (select id, description, created_date from t1)
  loop
    l_tab(cur_rec.id) := cur_rec;
  end loop;

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  id=' || v.id ||
                         '  description=' || rpad(v.description,8,' ') ||
                         '  created_date=' || v.created_date);
  end loop;
end;
/
index=11  id=11  description=ELEVEN    created_date=02-JAN-21
index=12  id=12  description=TWELVE    created_date=02-JAN-21
index=13  id=13  description=THIRTEEN  created_date=02-JAN-21
index=14  id=14  description=FOURTEEN  created_date=02-JAN-21
index=15  id=15  description=FIFTEEN   created_date=02-JAN-21

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of t1%rowtype index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in (select id, description, created_date from t1) index i.id => i
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  id=' || v.id ||
                         '  description=' || rpad(v.description,8,' ') ||
                         '  created_date=' || v.created_date);
  end loop;
end;
/
index=11  id=11  description=ELEVEN    created_date=02-JAN-21
index=12  id=12  description=TWELVE    created_date=02-JAN-21
index=13  id=13  description=THIRTEEN  created_date=02-JAN-21
index=14  id=14  description=FOURTEEN  created_date=02-JAN-21
index=15  id=15  description=FIFTEEN   created_date=02-JAN-21

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of t1%rowtype index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in (select id, description, created_date from t1) when mod(i.id,2)=0 index i.id => i
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  id=' || v.id ||
                         '  description=' || rpad(v.description,8,' ') ||
                         '  created_date=' || v.created_date);
  end loop;
end;
/
index=12  id=12  description=TWELVE    created_date=02-JAN-21
index=14  id=14  description=FOURTEEN  created_date=02-JAN-21

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of t1%rowtype index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in (select id, description, created_date from t1) sequence => i
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  id=' || v.id ||
                         '  description=' || rpad(v.description,8,' ') ||
                         '  created_date=' || v.created_date);
  end loop;
end;
/
index=1  id=11  description=ELEVEN    created_date=02-JAN-21
index=2  id=12  description=TWELVE    created_date=02-JAN-21
index=3  id=13  description=THIRTEEN  created_date=02-JAN-21
index=4  id=14  description=FOURTEEN  created_date=02-JAN-21
index=5  id=15  description=FIFTEEN   created_date=02-JAN-21

PL/SQL procedure successfully completed.

SQL>

declare
  cursor c_cursor is
    select id, description, created_date from t1;
    
  type tab_t is table of t1%rowtype index by pls_integer;
  l_tab  tab_t;
begin
  -- Populate the collection.
  l_tab := tab_t(
for i in c_cursor index i.id => i
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  id=' || v.id ||
                         '  description=' || rpad(v.description,8,' ') ||
                         '  created_date=' || v.created_date);
  end loop;
end;
/
index=11  id=11  description=ELEVEN    created_date=02-JAN-21
index=12  id=12  description=TWELVE    created_date=02-JAN-21
index=13  id=13  description=THIRTEEN  created_date=02-JAN-21
index=14  id=14  description=FOURTEEN  created_date=02-JAN-21
index=15  id=15  description=FIFTEEN   created_date=02-JAN-21

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of t1%rowtype index by pls_integer;
  l_tab  tab_t;

  l_sql     varchar2(32767);     
  l_cursor  sys_refcursor;
begin
  l_sql := 'select id, description, created_date from t1';

  -- Populate the collection.
  open l_cursor for l_sql;
  l_tab := tab_t(
for i t1%rowtype in values of l_cursor index i.id => i
); 
  close l_cursor;
  
  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  id=' || v.id ||
                         '  description=' || rpad(v.description,8,' ') ||
                         '  created_date=' || v.created_date);
  end loop;
end;
/
index=11  id=11  description=ELEVEN    created_date=02-JAN-21
index=12  id=12  description=TWELVE    created_date=02-JAN-21
index=13  id=13  description=THIRTEEN  created_date=02-JAN-21
index=14  id=14  description=FOURTEEN  created_date=02-JAN-21
index=15  id=15  description=FIFTEEN   created_date=02-JAN-21

PL/SQL procedure successfully completed.

SQL>

declare
  type tab_t is table of t1%rowtype index by pls_integer;
  l_tab  tab_t;

  l_sql  varchar2(32767);
begin
  l_sql := 'select id, description, created_date from t1';

  -- Populate the collection.
  l_tab := tab_t(
for i t1%rowtype in (execute immediate l_sql) index i.id => i
); 

  -- Display the contents of the collection.
  for i,v in pairs of l_tab loop
    dbms_output.put_line('index=' || i || '  id=' || v.id ||
                         '  description=' || rpad(v.description,8,' ') ||
                         '  created_date=' || v.created_date);
  end loop;
end;
/
Error report -
ORA-06550: line 13, column 33:
PLS-00801: internal error [*** ASSERT at file pdz4.c, line 3518; Self is null.; Xanon__0x1fc208ea0__AB[10, 38]]
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!