Allow for iterating associative array key/value pairs

Lukas Eder

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); 

William Robertson

Nice idea. Perhaps the syntax could be similar to forall<code>, e.g.

    for i in indices of somecollection loop

Jeffrey Kemp

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;

/

Lukas Eder

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.

blessed DBA

Nice idea. Perhaps the syntax could be similar to bulk collection<code>, e.g.

SELECT employee_id

BULK COLLECT INTO l_employee_ids

William Robertson

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_ids

I’m not following. What would the loop look like then?

William Robertson

Alternative syntax akin to FORALL’s syntax as suggested by Jeff Kemp

You’re welcome 

moc-moc-a-moc

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.