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

April 16, 2012 at 10:29 am | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | 5 Comments
Tags: , , , , , , , , , , , , ,

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


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;

Advertisements

5 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Reblogged this on Christoph's 2 Oracle Cents.

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

  3. I like it better with

    while
    l_key is not null
    loop

    And no “exit when”

  4. 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;

    /

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Entries and comments feeds.

%d bloggers like this: