Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
PL/SQL - Implement FOR .. OF loops from Ada 2012

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).
Comments
-
I think I'd prefer to reuse the
INDICES OF
syntax fromFORALL
(and maybe find a use forVALUES OF
while we're at it), but yes, a nice idea that shouldn't be hard to implement. -
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 .
-
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.
-
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.
-
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.
-
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;
-
I think I'd prefer to reuse the
INDICES OF
syntax fromFORALL
(and maybe find a use forVALUES 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
-
I think I'd prefer to reuse the
INDICES OF
syntax fromFORALL
(and maybe find a use forVALUES 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.
-
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.
They also added a few other iterator syntaxs
- PAIRS OF for hash Associative Arrays
- something resembling a classic C for() loop
MK