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!!
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 ...
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.
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.
A sample control file from ORACLE :
-- $Header: ulcase6.ctl 09-mar-99.22:22:13 mjaeger Exp $ ulcase6.ctl
-- Copyright (c) Oracle Corporation 1991, 1997, 1999. All Rights Reserved.
-- ulcase6.ctl -
-- MODIFIED (MM/DD/YY)
-- mjaeger 03/08/99 - bug 808870: OCCS: convert tabs, no long lines
-- msakayed 09/02/97 - remove nullif on empno
-- jhealy 01/27/95 - merge changes from branch 1.2.720.1
-- ksudarsh 11/06/92 - infile is ulcase6
-- cheigham 08/28/91 - Creation
into table emp
sorted indexes (empix)
mgr position(27:30) nullif mgr=blanks,
sal position(32:39) nullif sal=blanks,
comm position(41:48) nullif comm=blanks,
deptno position(50:51) nullif empno=blanks