8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Qualified Expressions Enhancements in Oracle Database 21c
Oracle database 21c makes it even easier to populate collections using qualified expressions.
- Before We Begin
- Setup
- Positional Notation for Associative Array
- Basic Iterator
- Index Iterator
- Sequence Iterator
- Nested Tables and Varrays
- Populating Collections From Database Tables
Related articles.
- Qualified Expressions Enhancements in Oracle Database 21c (part 1)
- Qualified Expressions Enhancements in Oracle Database 21c (part 2)
- Qualified Expressions (Populating Record Types and Associative Arrays) in PL/SQL in Oracle Database 18c
- FOR LOOP Iteration Enhancements in Oracle Database 21c
- Collections in Oracle PL/SQL
- Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle
Before We Begin
This article makes some assumptions about prior knowledge.
- You have an understanding of the different types of Oracle collections, described here.
- You have an understanding of qualified expressions, introduced in 18c and described here.
- You have an understanding of the new
FOR LOOP
iteration enhancements introduced in Oracle database 21c, described here. - You have an understanding of bulk binds, described here.
Setup
Some of the examples in this article 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>
For more information see:
- Cursor Iteration Controls
- Qualified Expressions Overview
- Qualified Expressions Enhancements in Oracle Database 21c (part 1)
- Qualified Expressions Enhancements in Oracle Database 21c (part 2)
- Qualified Expressions (Populating Record Types and Associative Arrays) in PL/SQL in Oracle Database 18c
- FOR LOOP Iteration Enhancements in Oracle Database 21c
- Collections in Oracle PL/SQL
- Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle
Hope this helps. Regards Tim...