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

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Peter.

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

Last updated: June 03, 2020 - 10:59 am UTC

Version: 9.2.0

Viewed 50K+ 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 Tom 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.



Rating

  (3 ratings)

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

Comments

Peter, March 21, 2007 - 2:37 am UTC

Thanks
Peter

Associative Array

Siva, August 01, 2017 - 10:37 am UTC

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

Rajasekhar, June 01, 2020 - 3:08 pm UTC

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

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library