Re: ORA-01405: fetched column value is NULL

Sharkbones (sharkbones_at_aol.com) wrote:
: When I run the reports under ORACLE 7, it seems that the warning
: "ORA-01405: fetch column value is NULL" is returning a negative SQLCODE
 ...
: This is frustrating. Can anybody offer a solution to this?
: Thanks in advance,

Yes. I believe Oracle introduced this "feature" in Oracle7. That would explain why you didn't see it in V6. You need to use indicator variables for any columns that are selected that can possibly have a null value.

For example, in v6 you had:

select COLUMN
into :HOST_VAR
from TABLE;

In Oracle7, in addition to declaring HOSTVAR in your SQL DECLARE SECTION you must also declare an indicator variable. I've never used Pro*Cobol so I don't know how exactly this is done, but I can tell you in Pro*C indicator variables should be declared as type "short" (a short integer). The Pro*C manual should discuss this. After you have declared this variable (I'll call it HOST_VAR_IND) you have to change the SQL statement above to:

select COLUMN
into :HOST_VAR:HOST_VAR_IND
from TABLE;

If COLUMN is null then HOST_VAR_IND will be set to -1. If COLUMN is not null but HOST_VAR is too small to contain the entire value then HOST_VAR_IND will be > 0. Otherwise, HOST_VAR_IND will be set to 0.

Since the program worked in v6 without checking for nulls, I would guess that you could get by by just declaring the indicator variables and referencing them in the INTO clauses.

• Dan