Re: Pro*C: ora-01405 when UNPACK_MESSAGE(empty string)

rs_arwar_at_hotmail.com (Rauf Sarwar) wrote in news:92eeeff0.0210021117.240022c8_at_posting.google.com:

> This is from Oracle's Error manual.
>
> ORA-01405: fetched column value is NULL
>
> Cause: The INTO clause of a FETCH operation contained a NULL value,
> and no indicator was used. The column buffer in the program remained
> unchanged, and the cursor return code was +2. This is an error unless
> you are running Oracle7 with DBMS=6, emulating version 6, in which
> case it is only a warning.
> Action: You may do any of the following:
>
> ·_Use the NVL function to convert the retrieved NULL to another value,
> such as zero or blank. This is the simplest solution.
> ·_Use an indicator to record the presence of the NULL. You probably
> should use this option when you want a specific action to be taken
> when a NULL arises.
> ·_Revise the cursor definition so that no columns possibly containing
> NULL values are retrieved.

However I know from experience this is not the only cause. This can happen any time you attempt to assign a null value to a host variable.

:hostvar := NULL;

will do it. This makes sense with numbers because most host languages don't have any way to distinguish between a null numeric field and zero, but with strings it may be legitimate so the way to handle it is to declare an exception name in the "DECLARE" section of your code and associate it with the -1405 exception:

nulls_in_hostvar EXCEPTION;
PRAGMA EXCEPTION_INIT (nulls_in_hostvar, -1405);

Then in your code, if you must assign a null value to a host variable:

BEGIN
  :hostvar := NULL;
EXCEPTION
  WHEN nulls_in_hostvar THEN
    NULL;
END; This will successfully assign a null value to your host variable.

-- 
Ken Denny
http://www.kendenny.com/