New PL/SQL Array Enhancements in Oracle Database 20c

I was catching up on some PL/SQL office hours the other day, and one of the topics was A Preview of Oracle Database 20c PL/SQL Enhancements.

The parts of this session that I was particularly interested in were:

• Extended Iterator Constructs
• Aggregate Qualified Expressions

Before we get started, it's worth noting these features are on version 20c of the Oracle Database, which has not yet been released. You can however boot up a preview version of this software from within Oracle Cloud.

Environment Set Up

To get set up, head over to your Oracle Cloud tenancy, assuming you have one. If you don't have one, you can sign up for a free trial and use this in your trial period.

I am assuming you already have a Virtual Cloud Network configured, so I won't be diving into that aspect. Once you are signed into your cloud environment, on the menu navigate to Bare Metal, VM, and Exadata from within the Oracle Database section.

From this landing page, click the button Create DB System. And from the dialog that appears, you will want to change some settings:

1. Change the shape to VM.Standard2.1
2. Storage Management Software needs to be Logical Volume Manager
3. Specify a virtual network you have configured. So that I can connect over the public internet, I specify one of my networks with a public subnet.
4. Give the database a hostname prefix

With those settings complete, click the Next button at the bottom of the page. It's this next page where you will be able to specify the database version you'd like, and specifically, 20c (Preview).

 

This process will take probably around 20 minutes to provision all the necessary resources.

Once that completes, we can go ahead and set up a connection in SQL Developer. Within OCI, we can get the Easy Connect String. This will include the internal OCI hostname. So we just need to substitute that with the public IP address.

So navigate to the database and click on the DB Connection button. 

Here, OCI will give you two versions. Go ahead and copy the Easy Connect version, and then you will want to just substitute it with the public IP address assigned to your DB System. You can determine your public IP address by navigating to the nodes section of the DB System details.

My connect string ends up looking like this:  168.138.10.167:1521/DB0930_mel17s.subnet.vcn.oraclevcn.com. This is translated into SQL Developer connection that resembles like this

 

Test and if all panned out, you should be able to connect.

Extended Iterator Constructs

With all the preliminary work done, now we can get to the fun part. Testing the new language enhancements. We can see the full list of new features here: https://docs.oracle.com/en/database/oracle/oracle-database/20/newft/pl-sql.html

The documentation states:

These iteration controls available are:

    Stepped range iteration controls
    Single expression iteration controls
    Collections iteration controls
    Cursor iteration controls

Multiple iteration controls may be chained together.

New stopping and skipping predicate clauses have been added.

The new mutability property of an iterand determines whether or not it can be assigned to in the loop body.

An iterand type can be implicitly or explicitly declared.

Here are some examples I had a play around with:

Multiple iteration controls can be chained together:

declare
begin
    for i in
        1..2,
        5..7,
        9..10
    loop
        dbms_output.put_line(i);
    end loop;
end;
/

Output:

While and when constructs support in a for loop

Currently, you can have a while loop with a condition. e.g. while (condition). Now, the for loop is extended to support both a while and the new when construct. While behaves the same and when allows you to skip certain rows.

declare
    l_task_complete boolean := false;
begin
    for i in 1..10 while not l_task_complete
    loop
        dbms_output.put_line(i);
        if i = 5 then
            l_task_complete := true;
        end if;
    end loop;
    dbms_output.put_line('DONE');
end;
/

Output:

 

Then the when construct. For a set of values, run the body of the loop in some condition.

declare
begin
    for i in 1..10 when mod(i, 2) = 0
    loop
        dbms_output.put_line(i);
    end loop;
    dbms_output.put_line('DONE');
end;
/

Output:

 

 

 

You can make your iterator variable mutable, meaning it can be modified in the body of the loop. As per the office hours, each iteration will automatically increment by the step (default 1), so if you do modify it in the body of the loop, you need to also substract it by the step counter.

The default/assumed value is immutable, but you could also be more specific and specify that in your loops moving forward.

declare
begin
    for i mutable in 1..5
    loop
        dbms_output.put_line(i);

        -- equivalent to changing the step
        -- but just to show it is mutable
        i := i+2;
    end loop;
    dbms_output.put_line('DONE');
end;
/

Output:

 

 

 

Now you can specify both a step counter with the by operator, and also be explicit with the data type of the iterand (default is integer).

declare
begin
    for i number in 1..3 by 0.5
    loop
        dbms_output.put_line(i);
    end loop;
    dbms_output.put_line('DONE');
end;
/

Output:

Finally, there are a few new constructs which make a lot nicer to iterate over complex types.

• pairs of
• values of
• indices of

declare
    type t_person is record (name varchar2(20),date_added date);
    type t_ppl is table of t_person index by pls_integer;
    l_everyone t_ppl;

    function new_person(p_name in varchar2)
    return t_person
    as
        l_person t_person;
    begin

        l_person.name := p_name;
        l_person.date_added := sysdate;

        return l_person;
    end new_person;
begin
    l_everyone(1) := new_person('Thomas');
    -- l_everyone(2) := new_person('Peter');
    l_everyone(3) := new_person('Mary');

    dbms_output.put_line('indices');
    for i in indices of l_everyone
    loop
        dbms_output.put_line('index: ' || i );
    end loop;

    dbms_output.put_line('-');
    dbms_output.put_line('values');
    for pers in values of l_everyone
    loop
        dbms_output.put_line('name: ' || pers.name);
    end loop;

    dbms_output.put_line('-');
    dbms_output.put_line('pairs');
    for i,pers in pairs of l_everyone
    loop
        dbms_output.put_line('index: ' || i || '; name: ' || pers.name);
    end loop;
end;
/

Output:

Aggregate Qualified Expressions 

Oracle defines this (from the docs) as:

Qualified expressions improve program clarity and developer productivity by providing the ability to declare and define a complex value in a compact form where the value is needed. 

We can combine all these enhancements into a new array constructor loop expression, to provide a short hand way to quickly initialise an array. 

In it's most basic from you can create an array with the index and value having the same value like so:

declare
    l_num_list dbms_sql.number_table;
begin
    l_num_list := dbms_sql.number_table( for i in 1..10 => i );
    dbms_output.put_line(l_num_list.count);
end;
/

More than likely, you will want to have an index independent from the value. It which case, you can use the index clause on the loop spec.

declare
    type t_person is record (name varchar2(20),date_added date);
    type t_ppl is table of t_person index by pls_integer;
    l_everyone t_ppl;
    l_everyone2 t_ppl;

    function new_person(p_name in varchar2)
    return t_person
    as
        l_person t_person;
    begin

        l_person.name := p_name;
        l_person.date_added := sysdate;

        return l_person;
    end new_person;
begin
    l_everyone(1) := new_person('Thomas');
    l_everyone(2) := new_person('Peter');
    l_everyone(3) := new_person('Mary');

    l_everyone2 := t_ppl(for i,v in pairs of l_everyone index i => v);

end;
/

The third example, is the sequence clause. If you are chaining collections together in your constructor, you could have an index duplicated in which case you will lose some entries following the previous example. I will be honest though, I couldn't manage to get this working, so have skipped over this example snippet for now.