PL/SQL - Implement FOR .. OF loops from Ada 2012

Jon Theriault

To create a block in PL/SQL to loop through a possibly sparse table you'd need to write the following:

declare

     index_l     pls_integer;

     value_l     varchar2 ( 200 );

     -- mytable_l is a table declared outside of the block

begin

     if mytable_l is not null then

          index_l := mytable_l.first();

     end if;

     while index_l is not null loop

          value_l := mytable_l ( index_l );

          do_something ( value_l );

          index_l := mytable_l.next ( index_l );

     end loop;

end;

There's about twelve lines of code for a basic loop.  This is actually a lot of code for what is a relatively common task and is the most common complaint I hear from people new to PL/SQL - especially those who just came out of college with the FOR ... EACH loop from the Java/.net world.  For existing developers I'd argue with each line of code it becomes harder to read and more likely for a developer to fat finger something (or forget to add the call to next, etc).  Ada 2012 introduced the FOR OF iteration which I'd imagine would be functionally identical but look something like this in PL/SQL.

if mytable_l is not null

     for value_l of mytable_l loop

          do_something ( value_l );

     end loop;

end if;

We've more than halved the amount of code and didn't need to create a block just to keep value_l a local variable within the loop (PL/SQL would ensure that).

As the Ada 2012 Rationale explains their example code:

Not only is this just five lines of text rather than nine or eleven, the key point is that the possibility of making various errors of detail is completely removed.

The mechanisms by which this magic abstraction is achieved are somewhat laborious and it is anticipated that users will take a cookbook approach (show us how to do it, but please don't explain why – after all, this is the approach taken with boiling an egg, we can do it without deep knowledge of the theory of coagulation of protein material).

William Robertson

I think I'd prefer to reuse the INDICES OF syntax from FORALL (and maybe find a use for VALUES OF while we're at it), but yes, a nice idea that shouldn't be hard to implement.

GregV

In your sample code, this part is useless:

if mytable_l is not null then

  index_l := mytable_l.first();

end if;

FIRST returns NULL if the collection is empty, so your WHILE loop wouldn't be executed.

I imagine we could save the same IF condition with your suggestion. The bit:

for value_l of mytable_l loop

would do the same test implicitely .

Jon Theriault

In your sample code, this part is useless:

if mytable_l is not null then

  index_l := mytable_l.first();

end if;

FIRST returns NULL if the collection is empty, so your WHILE loop wouldn't be executed.

I imagine we could save the same IF condition with your suggestion. The bit:

for value_l of mytable_l loop

would do the same test implicitely .

Yes, I'd think if the collection is empty the new FOR ... OF I'm asking for would just not iterate through anything in the collection.  If the collection isn't initialized then I'd think we'd still want to raise an ORA-06531.

Here's a quick little example of why I always add the is not null check (especially if the table was passed in as a parameter).  This was run on 12.1

Error starting at line : 1 in command -

declare

   type mytable_t is table of number;

   mytable_l mytable_t;

   mytable_idx pls_integer;

begin

   mytable_idx := mytable_l.first();

end;

Error report -

ORA-06531: Reference to uninitialized collection

ORA-06512: at line 6

06531. 00000 -  "Reference to uninitialized collection"

*Cause:    An element or member function of a nested table or varray

           was referenced (where an initialized collection is needed)

           without the collection having been initialized.

*Action:   Initialize the collection with an appropriate constructor

           or whole-object assignment.

GregV

Yes, I'd think if the collection is empty the new FOR ... OF I'm asking for would just not iterate through anything in the collection.  If the collection isn't initialized then I'd think we'd still want to raise an ORA-06531.

Here's a quick little example of why I always add the is not null check (especially if the table was passed in as a parameter).  This was run on 12.1

Error starting at line : 1 in command -

declare

   type mytable_t is table of number;

   mytable_l mytable_t;

   mytable_idx pls_integer;

begin

   mytable_idx := mytable_l.first();

end;

Error report -

ORA-06531: Reference to uninitialized collection

ORA-06512: at line 6

06531. 00000 -  "Reference to uninitialized collection"

*Cause:    An element or member function of a nested table or varray

           was referenced (where an initialized collection is needed)

           without the collection having been initialized.

*Action:   Initialize the collection with an appropriate constructor

           or whole-object assignment.

Fair point, maybe this "uninitialized collection" thing could also be removed then. The collection could get initialized the first time it is used.

Sven W.

Yes, I'd think if the collection is empty the new FOR ... OF I'm asking for would just not iterate through anything in the collection.  If the collection isn't initialized then I'd think we'd still want to raise an ORA-06531.

Here's a quick little example of why I always add the is not null check (especially if the table was passed in as a parameter).  This was run on 12.1

Error starting at line : 1 in command -

declare

   type mytable_t is table of number;

   mytable_l mytable_t;

   mytable_idx pls_integer;

begin

   mytable_idx := mytable_l.first();

end;

Error report -

ORA-06531: Reference to uninitialized collection

ORA-06512: at line 6

06531. 00000 -  "Reference to uninitialized collection"

*Cause:    An element or member function of a nested table or varray

           was referenced (where an initialized collection is needed)

           without the collection having been initialized.

*Action:   Initialize the collection with an appropriate constructor

           or whole-object assignment.

I agree with the general notion, that less code is better.

There are a few solutions already. Currently I don't see much need for improvement in the FOR LOOP syntax.

It has more to do with modularization then with the FOR loop construct itself.

It also depends heavily on how the collection is populated in the first place and what is done with it later.

For example usually my code on a higher modularization level looks like this

do_something(collection) 

instead of

iterate over collection  do_something (record)

Of cause often once inside a module, we have the need to iterate over the elements of the collection. In case of a sparse collection (they are rare and sometimes can be avoided) the iteration often looks like this.

index := collection.first;<<iterate_collection>>loop   do_some_mapping(collection(index), second_collection);   exit when index = collection.last();  index := collection.next(index);end loop iterate collection;

Or if we want to do soemthing like an DML then more like this

forall i in indices of collection  insert into myTable(col1,col2) values (collection(i).name, collection(i).value);

In several cases it is useful instead of deleting records from a collection (so that you get a sparse collection), you instead create an index collection. And then loop over the index collection if needed.

Sven W.

Yes, I'd think if the collection is empty the new FOR ... OF I'm asking for would just not iterate through anything in the collection.  If the collection isn't initialized then I'd think we'd still want to raise an ORA-06531.

Here's a quick little example of why I always add the is not null check (especially if the table was passed in as a parameter).  This was run on 12.1

Error starting at line : 1 in command -

declare

   type mytable_t is table of number;

   mytable_l mytable_t;

   mytable_idx pls_integer;

begin

   mytable_idx := mytable_l.first();

end;

Error report -

ORA-06531: Reference to uninitialized collection

ORA-06512: at line 6

06531. 00000 -  "Reference to uninitialized collection"

*Cause:    An element or member function of a nested table or varray

           was referenced (where an initialized collection is needed)

           without the collection having been initialized.

*Action:   Initialize the collection with an appropriate constructor

           or whole-object assignment.

One alternative is to move the initialization problem into the exception handler.

Eg.

begin   index := myCollection.first;exception when COLLECTION_IS_NULL then    null; -- do nothingend;

Mike Kutz

I think I'd prefer to reuse the INDICES OF syntax from FORALL (and maybe find a use for VALUES OF while we're at it), but yes, a nice idea that shouldn't be hard to implement.

William Robertson wrote:...  (and maybe find a use for VALUES OF while we're at it) ...

(APEX) I believe a replacement Process for the stock MRD could use VALUES OF

forall i values of apex_application.g_f02

  delete from emp where empno = i;

MK

Jon Theriault

I think I'd prefer to reuse the INDICES OF syntax from FORALL (and maybe find a use for VALUES OF while we're at it), but yes, a nice idea that shouldn't be hard to implement.

I'd rather skip the INDICIES OF syntax for this one.  There are plenty of ways to loop with indexes already and looking at your profile it looks like you could probably recite them from memory.   The compiler knows the type of thing in the collection.  Just give me the thing and let me be "lazy like a fox". 

Also as I mentioned in the request it's not that it's "nice to have" at this point but after using Java, .net, and PL/SQL it feels like it's now a glaring omission.  Since on its surface it seems easy enough to add I'm hoping it would be low hanging fruit to get other developers on my team to stop complaining about how "old" PL/SQL is.  Again, not trying to start a war about PL/SQL vs other languages (I get that enough from my team and since I'm posting here you'd be preaching to the choir) just think it would help get more people using the product and make existing users happier. 

Mike Kutz

I'd rather skip the INDICIES OF syntax for this one.  There are plenty of ways to loop with indexes already and looking at your profile it looks like you could probably recite them from memory.   The compiler knows the type of thing in the collection.  Just give me the thing and let me be "lazy like a fox". 

Also as I mentioned in the request it's not that it's "nice to have" at this point but after using Java, .net, and PL/SQL it feels like it's now a glaring omission.  Since on its surface it seems easy enough to add I'm hoping it would be low hanging fruit to get other developers on my team to stop complaining about how "old" PL/SQL is.  Again, not trying to start a war about PL/SQL vs other languages (I get that enough from my team and since I'm posting here you'd be preaching to the choir) just think it would help get more people using the product and make existing users happier. 

20c (preview) documentation shows that they went with the INDICIES OF syntax.

https://docs.oracle.com/en/database/oracle/oracle-database/20/lnpls/plsql-control-statements.html#GUID-3F424C1F-9C0F-400…

They also added a few other iterator syntaxs

1. PAIRS OF for hash Associative Arrays
2. something resembling a classic C for() loop

MK