Monday, February 22, 2021

Qualified Expressions Enhancements in Oracle Database 21c

Oracle Database 21c, Oracle Database Tutorial and Material, Oracle Database Preparation, Oracle Database Learning, Oracle Database Career

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.

Oracle Database 21c, Oracle Database Tutorial and Material, Oracle Database Preparation, Oracle Database Learning, Oracle Database Career
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>

Related Posts

0 comments:

Post a Comment