Home » Articles » 21c » Here

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...

Back to the Top.

Created: 2021-01-06  Updated: 2022-04-05

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer