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:
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;
/
Reblogged this on Christoph's 2 Oracle Cents.
Thank you very much Christoph. Exactly what I was looking for.
I like it better with
…
while
l_key is not null
loop
…
And no “exit when”
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;