Home » Developer & Programmer » Precompilers, OCI & OCCI » Oracle OCI programming returning null value into define variable (Oracle 10g, RHEL 5.6)
Oracle OCI programming returning null value into define variable [message #638663] Wed, 17 June 2015 04:26 Go to next message
clancypc
Messages: 36
Registered: December 2006
Member
Hi,
I have a query that returns a value into a define variable, but if there is no value in the database for that field it inserts null. On Solaris this works fine, but I have migrated my code to Red Hat and it is behaving differently. The code is:
if ( RtrnValue == OCI_NO_DATA )
{
    return (FALSE);
}
else if ( RtrnValue == OCI_SUCCESS )
{
 
    switch (Migrated)
    {
        case 'Y':
            /* This cli is marked as migrated, so convert it as normal */
                strcpy( CliRec->AccountNumber, Account );
            return (TRUE);
        case 'N':
            /* I dont think that this will happen, but it potentially could 
            ** If it does then update the cli, set the migrated field to yes */
        default:
            /* The value should come through as null if the migrated field is not set
            ** hopefully this will catch null values */
            strcpy( CliRec->AccountNumber, Account );
            return (FALSE);
    }
 
}
else if ( Migrated == NULL )
{
    strcpy( CliRec->AccountNumber, Account );
    return (FALSE);
}
else
{
    check_err( OraErrorHandlePtr, RtrnValue);
    return (FALSE);
}


What predominently happens is the code falls through to the:
else if Migrated == NULL
statement and executes that statement and returns FALSE, however on Red Hat it does not recognise that Migrated == NULL and falls through to the check_err statement which then prints out the message:
ERROR - ORA-01405: fetched column value is NULL
So the value is NULL but as far as Red Hat is considered it isnt. Any ideas anybody?
Thanks
Peter
Re: Oracle OCI programming returning null value into define variable [message #638667 is a reply to message #638663] Wed, 17 June 2015 09:30 Go to previous messageGo to next message
dws1
Messages: 15
Registered: July 2012
Junior Member
There are only two correct ways of handling NULLs: (1) use a function like NVL to return a suitable value instead of a NULL; (2) use an indicator variable. Anything else will give undefined behaviour.

From the code - it's difficult to be certain with key parts of the code missing - but it seems you're relying on a specific bit pattern to determine if a NULL has been returned. This is not correct; it is undefined behaviour, and you must instead use one of the two above approaches.
Re: Oracle OCI programming returning null value into define variable [message #638668 is a reply to message #638667] Wed, 17 June 2015 10:01 Go to previous message
clancypc
Messages: 36
Registered: December 2006
Member
Aah, I just thought of that myself this afternoon and added in a call to the nvl funtion in the select statement which is working. I just came back to post that I had a workaround.
Thanks for your time.
Previous Topic: Cannot get one command line parameter from a response file
Next Topic: ProC Reading CLOB in chunks
Goto Forum:
  


Current Time: Wed Dec 13 23:03:07 CST 2017

Total time taken to generate the page: 0.06943 seconds