problem loading data using sqlldr

[crsdev]

I don't know if it is data type error.
Here is the situation:

-the rows load fine if a particular field in the row is 0.0
-but since it's not always 0.0, whenever it's not the row does not get loaded.

I get the follwoing message in log file
Rejected - Error on table T_XXXX, column SOME_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected

-But if i go to the txt file and make it 0.0 the row loads fine without touching the SOME_DATE field.

-The column in the table is declared as NUMBER.

THanks in advance.

 

[Turkbear]

Hi,
It may depend on your ctl file specified reading method
( Positional, Comma separated, etc) but, if there is no value where the Number is expected use the NULLIF cause:

For positional ( for a Date datatype):
BIRTH_DT POSITION(9:16) DATE 'mmddyyyy' NULLIF BIRTH_DT = BLANKS

By the way, why are you using NUMBER for a DATE?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[crsdev]

Thanks for your responce turkbear,
I found my mistake it was in control file.