PL/SQL Associative array validate if index exist

Andy Chaves picture Andy Chaves · Dec 3, 2013 · Viewed 11.4k times · Source

I have this Associative array 3-d

type v_arr_class is table of varchar2(255) index by varchar2(255);
type v_arr_component is table of v_arr_class index by varchar2(255);
type v_arr_property is table of v_arr_component index by varchar2(255);

v_arr_local_rec v_arr_property;

I need to validate if an index exist

if(v_arr_local_rec('class')('component')('property') exist) then

do this... 

end if

not much info about associative arrays found.

thanks in advance.

Answer

Vincent Malgrat picture Vincent Malgrat · Dec 3, 2013

You'll have to perform 3 existence tests to be sure not to trigger a NO_DATA_FOUND error:

SQL> DECLARE
  2     TYPE t_arr_class IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(255);
  3     TYPE t_arr_component IS TABLE OF t_arr_class INDEX BY VARCHAR2(255);
  4     TYPE t_arr_property IS TABLE OF t_arr_component INDEX BY VARCHAR2(255);
  5     v_arr_local_rec t_arr_property;
  6  BEGIN
  7     IF v_arr_local_rec.EXISTS('class')
  8        AND v_arr_local_rec('class').EXISTS('component')
  9        AND v_arr_local_rec('class')('component').EXISTS('property')
 10     THEN
 11        dbms_output.put_line('true');
 12     ELSE
 13        dbms_output.put_line('false');
 14     END IF;
 15  END;
 16  /

false

PL/SQL procedure successfully completed