This discussion is archived
3 Replies Latest reply: Mar 20, 2013 1:45 PM by Sg049 RSS

Associative Array, how to loop the records using its count ?

809543 Newbie
Currently Being Moderated
In the Associative Array, how to loop the records using its count ? for example
declare
    type population is table of number index by varchar2(64);
    city_population population;   
begin
    city_population('Samillve') := 200;
    city_population('Lindenhurst') := 300;    
    
    for i in 1 .. city_population.count
    loop
        dbms_output.put_line(city_population(i)); -- compiler error
    end loop;
end;
/
  • 1. Re: Associative Array, how to loop the records using its count ?
    rp0428 Guru
    Currently Being Moderated
    You don't - when indexed by VARCHAR2 you use FIRST, NEXT, PRIOR and LAST like the docs show you.
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#i20453
    Looping Through Collection Elements (PRIOR and NEXT Methods)
    PRIOR(n) returns the index number that precedes index n in a collection. NEXT(n) returns the index number that succeeds index n. If n has no predecessor, PRIOR(n) returns NULL. If n has no successor, NEXT(n) returns NULL.
    
    For associative arrays with VARCHAR2 keys, these methods return the appropriate key value; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.
    
    These methods are more reliable than looping through a fixed set of subscript values, because elements might be inserted or deleted from the collection during the loop. This is especially true for associative arrays, where the subscripts might not be in consecutive order and so the sequence of subscripts might be (1,2,4,8,16) or ('A','E','I','O','U').
    
    Example 5-32 Using PRIOR and NEXT to Access Collection Elements
    
    DECLARE
       TYPE NumList IS TABLE OF NUMBER;
       n NumList := NumList(1966,1971,1984,1989,1999);
    BEGIN
       DBMS_OUTPUT.PUT_LINE('The element after #2 is #' || n.NEXT(2));
       DBMS_OUTPUT.PUT_LINE('The element before #2 is #' || n.PRIOR(2));
       n.DELETE(3);
         -- Delete an element to show how NEXT can handle gaps.
       DBMS_OUTPUT.PUT_LINE
         ('Now the element after #2 is #' || n.NEXT(2));
       IF n.PRIOR(n.FIRST) IS NULL THEN
          DBMS_OUTPUT.PUT_LINE
            ('Can''t get PRIOR of the first element or NEXT of the last.');
       END IF;
    END;
    /
  • 2. Re: Associative Array, how to loop the records using its count ?
    Justin Cave Oracle ACE
    Currently Being Moderated
    Which would look something like
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2      type population is table of number index by varchar2(64);
      3      city_population population;
      4      l_index varchar2(64);
      5  begin
      6      city_population('Samillve') := 200;
      7      city_population('Lindenhurst') := 300;
      8      l_index := city_population.first;
      9      while( l_index IS NOT NULL )
     10      loop
     11          dbms_output.put_line(city_population(l_index ));
     12          l_index := city_population.next(l_index);
     13      end loop;
     14* end;
    SQL> /
    300
    200
    
    PL/SQL procedure successfully completed.
    Justin
  • 3. Re: Associative Array, how to loop the records using its count ?
    Sg049 Explorer
    Currently Being Moderated
    In For loop "i" act as index and it is a number but your associative array indexed with varchar2. So it won't compile.

    you might have rewrite the block something like below
    declare
        type population is table of number index by varchar2(64);
        city_population population; 
        l_idx varchar2(64);
    begin
        city_population('Samillve') := 200;
        city_population('Lindenhurst') := 300;    
        
         
        l_idx := city_population.FIRST;
           while (l_idx is not null)
         loop
             dbms_output.put_line( city_population(l_idx) );
             l_idx := city_population.next(l_idx);
        end loop;
       
    end;
    Please refer this link for looping associative arrays:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:173586000346213111

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points