Handling Null values
Moderators: chulett, rschirm, roy
Handling Null values
Hi, I am trying to read a sequential file and updating a Oracle database using OCI stage.
The sequential file has null values in a field1 which is hardcoded as NULL. I am uploading this data to oracle which is a NUMBER field.
I tried to define the NULLABLE field as NULL in the sequential file stage. But still I am getting the following error
rnk_deqoci..Transformer_25: ORA-01722: invalid number
Can anyone of you please let me know how to handle such a situation. Sorry if this is too basic. You can guide me to the revelant documents and I will find it from ther. Thanks in advance
The sequential file has null values in a field1 which is hardcoded as NULL. I am uploading this data to oracle which is a NUMBER field.
I tried to define the NULLABLE field as NULL in the sequential file stage. But still I am getting the following error
rnk_deqoci..Transformer_25: ORA-01722: invalid number
Can anyone of you please let me know how to handle such a situation. Sorry if this is too basic. You can guide me to the revelant documents and I will find it from ther. Thanks in advance
Re: Handling Null values
"NULL" in your input stream is not the same as @null. Oracle is expecting a number field and your are giving it a "NULL" which is a string.dprasanth wrote:Hi, I am trying to read a sequential file and updating a Oracle database using OCI stage.
The sequential file has null values in a field1 which is hardcoded as NULL. I am uploading this data to oracle which is a NUMBER field.
I tried to define the NULLABLE field as NULL in the sequential file stage. But still I am getting the following error
rnk_deqoci..Transformer_25: ORA-01722: invalid number
Can anyone of you please let me know how to handle such a situation. Sorry if this is too basic. You can guide me to the revelant documents and I will find it from ther. Thanks in advance
Define your column in the sequential file stage as varchar, and then in the transformer define the output column as integer. if the in.column ='Null' then default it to zero or @NULL.
Sorry for the late reply. I tried the above and it worked. As Sun Rays mentioned, the field002 in the sequential file was VARCHAR and it is hardcoded as NULL. But in the database the field was defined as decimal.So in the input file sequential stage I defined the variable as VARCHAR and did the above. But I am getting this oracle error.king999 wrote:hi
i have tried using
If DSLink4.Field002 = "null" then IsNull(DSLink4.Field002 ) else DSLink4.Field002
and it worked
RNK_SEQOCI_V1..Transformer_29: ORA-01861: literal does not match format string.Is it under the scope of this forum to answer this or should I go to my DBA?
Thanks a ton to everyone to tried to help me out in this query
Actually, I didn't have null in input field, but null was HARDCODED as NULL, may be that is the reason why it worked for mesun rays wrote:it might have worked for you, because you might never had 'null' in input file, otherwise the same error mght have appeared.king999 wrote:hi
i have tried using
If DSLink4.Field002 = "null" then IsNull(DSLink4.Field002 ) else DSLink4.Field002
and it worked