Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K 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.6K Security Software
Allow for iterating associative array key/value pairs

Looping over associative arrays can be a bit of a hassle in PL/SQL sometimes, especially if they're indexed by a varchar2 type.:
SET SERVEROUTPUT ON
DECLARE
TYPE arr_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
l_arr arr_t;
l_idx VARCHAR2(30);
BEGIN
l_arr('a') := 'x';
l_arr('b') := 'y';
l_idx := l_arr.FIRST;
WHILE l_idx IS NOT NULL LOOP
dbms_output.put_line(l_idx || '=' || l_arr(l_idx));
l_idx := l_arr.NEXT(l_idx);
END LOOP;
END;
/
The FOR loop can define implicit loop variables without explicit type references, both when looping over numbers:
FOR i IN 1 .. 10 LOOP
dbms_output.put_line(i);
END LOOP;
Or over implicit cursors:
FOR rec IN (SELECT 1 i FROM dual) LOOP
dbms_output.put_line(rec.i);
END LOOP;
Why not also create such an implicit FOR loop for associative arrays? E.g. using this hypothetical syntax:
SET SERVEROUTPUT ON
DECLARE
TYPE arr_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
l_arr arr_t;
BEGIN
l_arr('a') := 'x';
l_arr('b') := 'y';
FOR key, value IN l_arr LOOP
dbms_output.put_line(key || '=' || value);
END LOOP;
-- Alternative syntax akin to FORALL's syntax as suggested by Jeff Kemp: https://community.oracle.com/ideas/21601#comment-1026797
FOR key IN INDICES OF l_arr LOOP
dbms_output.put_line(key || '=' || l_arr(key));
END LOOP;
END;
/
Quite a few other languages have this feature, including PHP:
foreach ($arr as $key => $value) {
echo $key;
}
Kotlin:
for ((key, value) in map) {
...
}
C#:
foreach(var item in myDictionary)
{
foo(item.Key);
bar(item.Value);
}
Java:
for (Entry<K, V> entry : map.entrySet()) {
System.out.println(entry.getKey());
System.out.println(entry.getValue());
}
Newer versions of ECMAScript:
Object.entries(obj).forEach(
([key, value]) => console.log(key, value)
);
UPDATE: This has been implemented in Oracle 20c.
Example 6-9 Index Iterator Choice Association in Qualified Expressions
This example creates a copy of vec with values incremented by N.
result := vec_t (FOR I,j IN PAIRS OF vec INDEX I => j+n);
This example creates a vector of the first N even numbers.
result := vec_t (FOR i IN 2.n BY 2 INDEX i/2 => i);
Comments
-
Nice idea. Perhaps the syntax could be similar to
forall<code>, e.g.
for i in indices of somecollection loop
-
Nice idea. Perhaps the syntax could be similar to
forall<code>, e.g.
for i in indices of somecollection loop
Upvoted. Agreed with William, the syntax can be kept simple and consistent with other syntax in PL/SQL, and we don't need a special "value" placeholder.
SET SERVEROUTPUT ON
DECLARE
TYPE arr_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
l_arr arr_t;
BEGIN
l_arr('a') := 'x';
l_arr('b') := 'y';
FOR key IN INDICES OF l_arr LOOP
dbms_output.put_line(key || '=' || l_arr(key));
END LOOP;
END;
/
-
Upvoted. Agreed with William, the syntax can be kept simple and consistent with other syntax in PL/SQL, and we don't need a special "value" placeholder.
SET SERVEROUTPUT ON
DECLARE
TYPE arr_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
l_arr arr_t;
BEGIN
l_arr('a') := 'x';
l_arr('b') := 'y';
FOR key IN INDICES OF l_arr LOOP
dbms_output.put_line(key || '=' || l_arr(key));
END LOOP;
END;
/
Very cool, I wasn't aware of that syntax. That should definitely be reused in the FOR LOOP, for consistency reasons. I'll update my suggestion.
-
Nice idea. Perhaps the syntax could be similar to
bulk collection<code>, e.g.
SELECT employee_id
BULK COLLECT INTO l_employee_ids
-
Nice idea. Perhaps the syntax could be similar to
bulk collection<code>, e.g.
SELECT employee_id
BULK COLLECT INTO l_employee_ids
blessed DBA wrote:Nice idea. Perhaps the syntax could be similar to
bulk collection<code>, e.g.
SELECT employee_idBULK COLLECT INTO l_employee_idsI’m not following. What would the loop look like then?
-
Alternative syntax akin to FORALL’s syntax as suggested by Jeff Kemp
You’re welcome
-
The lack of this feature makes it dangerous to loop through associative arrays.
If you want to use CONTINUE, you need to remember to duplicate your "loop increment" logic, or you'll generate an infinite loop:
SET SERVEROUTPUT ON
DECLARE
TYPE arr_t IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
l_arr arr_t;
l_idx VARCHAR2(30);
BEGIN
l_arr('a') := 'x';
l_arr('b') := 'y';
l_idx := l_arr.FIRST;
WHILE l_idx IS NOT NULL LOOP
-- skip anything beginning with x
CONTINUE WHEN l_arr(l_idx) LIKE 'x%'; -- looks reasonable, but ruh-roh! infinite loop!
dbms_output.put_line(l_idx || '=' || l_arr(l_idx));
l_idx := l_arr.NEXT(l_idx);
END LOOP;
END;
/
The correct loop would of course be:
l_idx := l_arr.FIRST;
WHILE l_idx IS NOT NULL LOOP
-- skip anything beginning with x
IF l_arr(l_idx) LIKE 'x%'
THEN
l_idx := l_arr.NEXT(l_idx);
CONTINUE;
END IF;
dbms_output.put_line(l_idx || '=' || l_arr(l_idx));
l_idx := l_arr.NEXT(l_idx);
END LOOP;
... but then you are having to duplicate your "increment" code, which is also Not A Good Thing.
If you want to avoid duplicating your code, you can (eg) raise and trap an exception inside your loop, or use a GOTO, but then you're not using CONTINUE, which is meant to be the tool for this job.
There should be a way to set up a safe, implicit, once-over loop for an associative array, to protect us from ourselves. IN INDICES OF would be perfect.