Oracle database 21c makes it even easier to populate collections using qualified expressions.
◉ Before We Begin
This post makes some assumptions about prior knowledge.
- You have an understanding of the different types of Oracle collections.
- You have an understanding of qualified expressions.
- You have an understanding of the new FOR LOOP iteration enhancements introduced in Oracle database 21c.
- You have an understanding of bulk binds.
◉ Setup
Some of the examples in this post use the following objects.
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;
The query below displays the data in the table.
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>
All of the examples are being run in SQLcl and assume you have the server output enabled.
SQL> set serveroutput on
◉ 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.
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;
/
◉ 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.
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>
The expression can evaluate to a different type. In the following example we use a collection of VARCHAR2, and populate it with the word equivalent of the iterand.
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>
We can use most of the new features of the FOR LOOP 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.
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>
◉ 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.
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>
The expressions can evaluate to different types. In the following example we use a collection of DATE indexed by VARCHAR2.
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>
We can use most of the new features of the FOR LOOP introduced in Oracle database 21c. The following example includes stepped iteration, skipping and stopping.
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>
◉ 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.
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>
The expression can evaluate to a different type. In the following example we use a collection of VARCHAR2, and populate it with the word equivalent of the iterand. Once again, the index is not related to the iterand.
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>
We can use most of the new features of the FOR LOOP 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.
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>
◉ 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.
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>
Attempting to use a basic iterator always results in an error.
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.
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.
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>
I'm not sure if this is intentional, or a bug.
◉ 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 BULK COLLECT to build an associative array from the contents of the T1 table. Unfortunately, the index of the associative array is just a sequence from 1 to N, rather than matching the primary key column.
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>
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 FOR LOOP to retrieve the rows and use them to populate the collection, using the ID value for the collection index for convenience. We can then display the contents of the collection.
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>
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 INDEX iterator to indicate we want the collection index to be based on the ID column from the query. The operand matches the rowtype of the query.
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>
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 WHEN clause to only match rows with an ID column value that is even. Of course, you could argue this would be better done in the query itself.
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>
If we didn't care about the collection index matching the ID column from the query, we could just use the SEQUENCE iterator to let it use a default sequence.
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>
In the previous examples we used an implicit cursor. The iterators work equally well with an explicit cursor, as demonstrated below.
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>
We can also use a REF CURSOR 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.
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>
The documentation suggests it should also be possible to use dynamic SQL using the EXECUTE IMMEDIATE command, but that seems to give errors when I try.
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>
Posts
