Quick Look: How to loop through an associative array indexed by varchar2.

Here is a quick example of how to loop through an associative array (index-by table) indexed by varchar2:

[sourcecode language=”sql”]

DECLARE
TYPE t_tbl IS TABLE OF VARCHAR(1) INDEX BY VARCHAR2(1);
l_tbl t_tbl;
l_key VARCHAR2(1);
BEGIN
l_tbl(‘a’) := 1;
l_tbl(‘b’) := 2;
l_tbl(‘c’) := 3;

l_tbl.delete(‘b’);

l_tbl(‘e’) := 4;

l_key := l_tbl.first;

LOOP
EXIT WHEN l_key IS NULL;
dbms_output.put_line(l_tbl(l_key));
l_key := l_tbl.next(l_key);
END LOOP;

END;

[/sourcecode]

5 thoughts on “Quick Look: How to loop through an associative array indexed by varchar2.

  • August 29, 2016 at 10:45 am
    Permalink

    Thank you very much Christoph. Exactly what I was looking for.

    Reply
  • December 15, 2016 at 3:00 am
    Permalink

    I like it better with

    while
    l_key is not null
    loop

    And no “exit when”

    Reply
  • October 26, 2017 at 8:22 am
    Permalink

    why it is not working with for loop

    /* Formatted on 10/26/2017 3:19:39 PM (QP5 v5.256.13226.35510) */
    DECLARE
    TYPE deptno IS TABLE OF NUMBER
    INDEX BY varchar2(10); –Associative Array

    dept deptno; –variable
    — i VARCHAR2 (10):=null;
    BEGIN
    dept (‘a’) := 1;
    dept (‘b’) := 2;

    — Print associative array:

    –i := dept.FIRST; –for while clause

    FOR i IN dept.FIRST..dept.LAST
    LOOP
    BEGIN
    DBMS_OUTPUT.PUT_LINE (‘dept’ || dept (i));
    — i := dept.NEXT(i); –for while clause
    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.put_line (dept (i));
    END;
    END LOOP;
    EXCEPTION
    WHEN OTHERS
    THEN
    DBMS_OUTPUT.put_line (sqlerrm);
    END;

    /

    Reply
    • October 26, 2017 at 8:42 am
      Permalink

      Mohua,
      you’re indexing by varchar2, which makes dept.first and dept.last characters. In your for loop you assigne detp.first to i, which needs to be an integer.
      If you want to index by varchar2, use a while loop. From the comments it looks like you went that way already.

      DECLARE
      TYPE deptno
      IS TABLE OF NUMBER INDEX BY VARCHAR2(10); –Associative Array
      dept DEPTNO; –variable
      i VARCHAR2 (10) := NULL;
      BEGIN
      DEPT (‘a’) := 1;

      DEPT (‘b’) := 2;

      — Print associative array:
      i := dept.first; –for while clause

      LOOP
      EXIT WHEN i IS NULL;

      dbms_output.PUT_LINE(DEPT(i));

      i := dept.NEXT(i);
      END LOOP;
      EXCEPTION
      WHEN OTHERS THEN
      dbms_output.PUT_LINE (SQLERRM);
      END;

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *