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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Peter.

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

Answered by: Tom Kyte - Last updated: June 03, 2020 - 10:59 am 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

  (3 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...

Processing with multiple attributes

June 01, 2020 - 3:08 pm UTC

Reviewer: Rajasekhar from India

Hello,
Can you please help in printing the multiple attribute of associate array.
When i am tried with below code getting no data found error as it is indexing the count of records. but actually it is indexed by a_rollno.

create table test2(a_id number(10),
a_name varchar2(30),
a_class varchar2(30),
a_rollno number(10));


insert into test2 values(1,'test','first',10);
insert into test2 values(2,'test1','second',20);
insert into test2 values(3,'test2','thgird',30);
insert into test2 values(4,'test3','four',40);


declare
type collection_test is table of test2%rowtype index by binary_integer;
collection_test1 collection_test;
cursor c_1 is select * from test2;
begin
for i in c_1
loop
collection_test1(i.a_rollno):=i;
end loop;
dbms_output.put_line('the count is '||collection_test1.count);
for i in c_test1.first .. c_test1.last
loop
dbms_output.put_line('the name is '||collection_test1(a_rollno).a_name);
end loop;
exception
when others then
raise_application_error(-20000,'The error is'||SQLERRM||' '||SQLCODE);
end;
/
Thanks


Chris Saxon

Followup  

June 03, 2020 - 10:59 am UTC

I get lots of PLS-00201: identifier errors trying to run that code!

For example of how to loop through an array with non-consecutive values, see:

https://livesql.oracle.com/apex/livesql/docs/lnpls/plsql-collections-and-records/composites1.html
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:173586000346213111

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here