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??
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 ...