Re: ORA-1405 fetched column value is null Pro*C

From: Singapore Airlines Ltd <engsys_at_merlion.singnet.com.sg>
Date: 1996/08/02
Message-ID: <4trocq$vpa_at_lantana.singnet.com.sg>#1/1

Hello Scott,

When you get a column value, which is null in the database, into a 'C' variable, you get this error. To overcome this you will have to use an indicator variable.

for the SQL stmt you have written,

>>                  EXEC SQL FETCH cur_address INTO :address_temp;

Use this
EXEC SQL FETCH cur_address INTO :address_temp indicator :ind_var_1;

where ind_var_1 is declared as short ( irrespective of the datatype of the column that is being fetched.

The indicator variable will have a values -1, 0, 1.

   -1 if the column value was null.
    0 if the fetch was successful
    1 if there is any truncation.

Hope this helps.

Cheers,
Prasad.

Scott T. Johnson (sjohns6_at_ibm.net) wrote:
: Can anyone help?
 

: ORA-01405: fetched column value is NULL

: Oracle 7.2 on HP-UX
: Code:
: struct
: {
: VARCHAR v_postalroute[5];
: } address_temp;

: EXEC ORACLE OPTION (ORACA=YES);
 
: oraca.oradbgf = 1; /* enable debug operations */
: oraca.oracchf = 1; /* gather cursor cache statistics */
: oraca.orastxtf = 3; /* always save the SQL statement */
:
 

: EXEC SQL DECLARE cur_address CURSOR FOR
: SELECT postal_route
: FROM pp_postplus p1, scott t1
: WHERE p1.sns_key(+) = t1.sns_key
: AND (address_type = 'M'
: OR address_type is NULL)
: AND mail_ind <> 'N'
: AND zip = ':p_zip'
: AND sub_type = ':p_subtype';
 

: EXEC SQL OPEN cur_address;
 

: EXEC SQL WHENEVER NOT FOUND DO break;
 

: for (;;)
: {
: EXEC SQL WHENEVER SQLERROR DO sql_error("Address Fetch");
: EXEC SQL FETCH cur_address INTO :address_temp;
: printf("\naddress: %s\n",address_temp.v_postalroute);
Received on Fri Aug 02 1996 - 00:00:00 CEST