What is an indicator variable and why should I use it?

When you SELECT or FETCH a NULL value into a host variable/array an "ORA-01405: fetched column values is NULL" run-time error will result. This behaviour was introduced with Oracle7 when you recompiled your programs with the DBMS=V7 (the default) precompiler option.

Here is an example of how indicator variables can be used to explicitly handle NULL values:

EXEC SQL SELECT ENAME, SAL, COMM
         INTO   :emp_name, :emp_sal, :emp_comm:comm_indicator
         FROM   emp;

if (comm_indicator == -1)
    pay = emp_sal;   /* emp_comm IS NULL */
else
    pay = emp_sal + emp_comm;

Another workaround is to use the NVL() function to prevent the selection of NULL values.