How to get NULL condition checks for PL/SQL associative array DS

Question

I have the following code snippet and I would like to know how to check for existence of an entry in the associative array

set serveroutput on;
DECLARE
    TYPE per_form_metric IS record
      (
              output_achieved_itd NUMBER,
              output_achieved_ptd NUMBER
      );
      TYPE per_form_metrics_tbl IS TABLE OF per_form_metric INDEX BY VARCHAR2(10) ;
      TYPE interval_number_tbl IS TABLE OF per_form_metrics_tbl INDEX BY VARCHAR2(10) ;
      TYPE ms_output_tbl IS TABLE OF interval_number_tbl INDEX BY VARCHAR2(100) ;
      g_ms_output_tbl ms_output_tbl ;

      l_per_f_rec        per_form_metric;
      l_per_f_tbl        per_form_metrics_tbl;
      l_per_int_tbl     interval_number_tbl;
      l_ms_out_tbl    ms_output_tbl;

 BEGIN
    l_per_f_rec.output_achieved_itd := 1000;
    l_per_f_rec.output_achieved_ptd := 1000;

    l_per_f_tbl('Period 1') := l_per_f_rec;

    l_per_int_tbl('Interval 1') := l_per_f_tbl;

    l_ms_out_tbl('1') := l_per_int_tbl;

    -- Now get me the output_achieved_itd for 1 , Interval 1, Period 1

    dbms_output.put_line(l_ms_out_tbl('1')('Interval 1')('Period 1').output_achieved_itd);

    dbms_output.put_line(l_ms_out_tbl('2')('Interval 1')('Period 1').output_achieved_itd);


END;

The output for this code is as below

Error report:
ORA-01403: no data found
ORA-06512: at line 31
01403. 00000 -  "no data found"
*Cause:    
*Action:
1000

How can I check if

l_ms_out_tbl('2')('Interval 1')('Period 1').output_achieved_itd 

exists?

I would like to say something like

IF (l_ms_out_tbl('2')('Interval 1')('Period 1').output_achieved_itd IS NOT NULL) THEN
    do something awesome
  ELSE 
    continue wallowing
  END IF;

Answer 1

Instead of evaluating for nullality, evaluate for existence:

IF (l_ms_out_tbl.EXISTS('2'))
THEN
   IF (l_ms_out_tbl('2').EXISTS('Interval 1'))
   THEN
      IF (l_ms_out_tbl('2')('Interval 1').EXISTS('Period 1'))
      THEN
         null; --do something awesome
      END IF;
   END IF;
ELSE 
    null; --continue wallowing
END IF;

or catch NO_DATA_FOUND:

begin
   dbms_output.put_line(l_ms_out_tbl('2')('Interval 1')('Period 1').output_achieved_itd)
   -- did something awesome
exception
   when NO_DATA_FOUND then
      null;
      -- continue wallowing
end;

Answer 2

Use exists collection method. E.g.:

declare
  type foo_t is table of varchar2(20) index by varchar2(20);
  v_foos foo_t;
begin
  v_foos('FOO') := 'this is foo';

  if v_foos.exists('FOO') then
    dbms_output.put_line('exists');
  else
    dbms_output.put_line('not exists');
  end if;
end;
/

How to apply that to nested associative arrays is left to the OP :)

See also:

• PL/SQL - Working with a string!
• Why is this check for null associative array in PL/SQL failing?