Friday, March 14, 2008

Accessing Non-exsits Item in Oracle Associative Array

Look at this piece of code:
 set serveroutput on
 declare
  type MONTH_TYPE is table of varchar(20) index by binary_integer;

  month_table   MONTH_TYPE;
begin
  month_table(1) := 'Jan';
  month_table(2) := 'Feb';

  if month_table(3) is null then
    dbms_output.put_line( 'March is not defined.' );
  end if;
end;
/
What you'll get? You may think the print line.

However, you'll get an error:
    ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9
Well, associative array is working the same way as table (is that why it's defined as TABLE of ...), and month_table is similar to select value from month_table into v..., so need to have an exception handling.
    set serveroutput on
 declare
   type MONTH_TYPE is table of varchar(20) index by binary_integer;

   month_table   MONTH_TYPE;
 begin
   month_table(1) := 'Jan';
   month_table(2) := 'Feb';

   if month_table(3) is null then
     dbms_output.put_line( 'March is not defined.' );
   end if;
 exception
   when NO_DATA_FOUND then
     dbms_output.put_line( 'March is not found.' );
 end;
 /
Then, you'll get: March is not found.