Here is a very peculiar problem I faced while loading a pipe delimited text file.
I received a file delimited by pipe to load from a mainframe system. First the file has spaces instead of nulls for dates, so it was not accepted, the mainframe person told me that it is not possible for him to generate nulls but he will generate a low hexadecimal value which should be treated as nulls. The file looked perfectly ok to me the data for that field looked :
||
which meant the same -nulls.
But sql loader wouldn't accept it, it would give the same error. I had to substitute all the || with the same || manually only then it was accepted.
Now I don't want to do this everytime I receive the file and also it is going to be loaded automatically by shell scripts, is there any way to get over this??
Thanks for any input on this!!
08-30-2001, 10:27 AM
pipo
that must be because of this 'low hex value' ...
if you can manage to have this file under unix, use vi, do [esc]:se list
that will show you all character codes, so you'll see if there is an invisible character between your 2 pipes, which I think is the case
but why does this person try to 'generate nulls' ??? null just means that the value is not filled, so if you have 2 pipes : ||, without anything between them, then it means that this value is null ...
08-30-2001, 10:33 AM
svk
Let the main frame guy send you spaces.
Handle those spaces in you sqlloader control file with if condition.
This may not be a good idea if the volumes are heavy and regular. because doing this will not allow you to use direct load.
08-30-2001, 10:56 AM
Knarayan
CAN YOU PLEASE GIVE ME AN EXAMPLE OF SQLLOADER CONTROL FILE WITH IF CONDITION AS I AM IN A HURRY AND CANNOT FIND ANYTHING IN DOCUMENTATION.
