Indicator variables
-------------------

Use these buggers to avoid ORA-1405 errors, which occur if you read a NULL value back
into a host variable.


- associate a optional indicator variable with any host variable

- each time the host variable is used in an SQL statement, a result code is placed
  in its associated indicator variable.

- an indicator variable is declared in the DECLARE SECTION as a short

EXEC SQL BEGIN DECLARE SECTION;

 varchar name[20];
 short   name_ind;  /* indicator value */

EXEC SQL END DECLARE SECTION;


Use in the SQL statement in either of the following ways:

EXEC SQL SELECT name INTO :name:name_ind from emp where empno = 463819;

or 

EXEC SQL SELECT name INTO  :name INDICATOR :name_ind from emp where empno = 463819;

One can then check (if you want) the status of the host variable after the SQL call:


if(name_ind == -1) printf("Name field is NULL - help\n");

The prescence of the indicator variable is enough to keep ORACLE happy.


You can avoid the ORACLE 1405 errors by using the precompiler flag DBMS=V6,
(as we do) but this is a retrograde step to keep old code running.