Skip to Main Content
  • Questions
  • processing associative arrays in loops

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Peter.

Asked: March 19, 2007 - 5:24 am UTC

Answered by: Tom Kyte - Last updated: August 01, 2017 - 2:05 pm UTC

Category: Developer - Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hello Tom,

how can I process an associative array in a loop? Because the index is not numeric, a "FOR i in array.First .. array.LAST" raises an exception:

DECLARE
TYPE string_assarrtype IS TABLE OF VARCHAR2 ( 25 )
INDEX BY VARCHAR2 ( 20 );
arr string_assarrtype;
BEGIN
arr ( 'abcd' ) := 'a';
arr ( 'bcd' ) := 'b';
arr ( 'cd' ) := 'c';

FOR i IN arr.FIRST .. arr.LAST LOOP -- => Raises ORA-06512
DBMS_OUTPUT.put_line ( arr ( i ) );
END LOOP;

END;


Thank You
Peter

and we said...

ops$tkyte%ORA10GR2> DECLARE
  2      TYPE string_assarrtype IS TABLE OF VARCHAR2 ( 25 ) INDEX BY VARCHAR2 ( 20 );
  3      arr      string_assarrtype;
  4      l_idx    varchar2(20);
  5  BEGIN
  6     arr ( 'abcd' )  := 'a';
  7     arr ( 'bcd' )  := 'b';
  8     arr ( 'cd' )    := 'c';
  9
 10      l_idx := arr.first;
 11      while (l_idx is not null)
 12      loop
 13          dbms_output.put_line( arr(l_idx) );
 14          l_idx := arr.next(l_idx);
 15      end loop;
 16  END;
 17  /
a
b
c

PL/SQL procedure successfully completed.



and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

March 21, 2007 - 2:37 am UTC

Reviewer: Peter from Vienna, Austria

Thanks
Peter

Associative Array

August 01, 2017 - 10:37 am UTC

Reviewer: Siva from India

Hi ,

Can you help me here.. i am trying to print 1st element of associative array. It is giving me the below error.

ORA-06550: line 18, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'

Below is the block:
declare

type test_array is table of varchar2(10) index by number(10);

test_array_1 test_array;

i number(10);
begin

test_array_1(567):='a';

test_array_1(89):= 'b';

test_array_1(3):= 'c';

i:= test_array_1.first;

dbms_output.put_line( test_array_1(i) ); --just i want to print 1 element in the array

end;

Chris Saxon

Followup  

August 01, 2017 - 2:05 pm UTC

The problem is way back in your type declaration:

declare
  type test_array is
    table of varchar2(10) index by number(10);
  test_array_1   test_array;
begin
  null;
end;
/

PLS-00315: Implementation restriction: unsupported table index type


You can't use number for the index type...

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here