Get key and value of associative arrays
My problem is that I want to get the key and value from the associative array, but I can find out how to get the value from the key. Here's what I find:
DECLARE
TYPE assoc_array IS TABLE OF VARCHAR2(30)
INDEX BY VARCHAR2(30);
state_array assoc_array;
BEGIN
state_array('Alaska') := 'Juneau';
state_array('California') := 'Sacramento';
dbms_output.put_line(state_array('Alaska'));
dbms_output.put_line(state_array('California'));
END;
This prints Juno and Sacramento, but I want something like this:
DECLARE
TYPE assoc_array IS TABLE OF VARCHAR2(30)
INDEX BY VARCHAR2(30);
state_array assoc_array;
BEGIN
state_array('Alaska') := 'Juneau';
state_array('California') := 'Sacramento';
for x in 1..state_array.count loop
dbms_output.put_line(state_array(x).key || state_array(x).value);
end loop;
END;
Is it possible ?. Thanks in advance!
+3
source to share
2 answers
There is actually a way, kindly consider the below code
declare
type assoc_array is table of varchar2(30) index by varchar2(30);
state_array assoc_array;
l_idx varchar2(30);
begin
state_array('Alaska') := 'Juneau';
state_array('California') := 'Sacramento';
l_idx := state_array.first;
while (l_idx is not null) loop
dbms_output.put_line('Key = ' || l_idx || ':Value = ' || state_array(l_idx));
l_idx := state_array.next(l_idx);
end loop;
end;
The output will be
Key = Alaska:Value = Juneau
Key = California:Value = Sacramento
+9
source to share
You cannot do this with associative arrays. Because, see below.
DECLARE
TYPE assoc_array IS TABLE OF VARCHAR2(30)
INDEX BY VARCHAR2(30);
state_array assoc_array;
BEGIN
state_array('Alaska') := 'Juneau';
state_array('California') := 'Sacramento';
for x in 1..state_array.count loop
dbms_output.put_line('x ='||x);
end loop;
END;
will print x = 1 x = 2
The Oracle does not know that x = 1 = Alaska.
You have to use a binary array to do something like this.
-1
source to share