Error uploading a file to Oracle with NULL date value

How can I upload to Oracle (11gR2) database table via sqlloader when date field has a null value.

I have attached the Control file , sample records and the error log.

All lines are getting rejected. As all lines have at least one empty Date value for the DATE fields f2, f4, f5, f6, f7 and f8.

The sqlload works fine when there are date values.
Control file:
OPTIONS (SKIP = 2, ERRORS = 1000, ROWS = 66796)
load data
 infile '/var/www//myfile.txt'
 fields terminated by ','
f2 DATE 'mm/dd/yyyy',
f4 DATE 'mm/dd/yyyy',
f5 DATE 'mm/dd/yyyy',
f6 DATE 'mm/dd/yyyy',
f7 DATE 'mm/dd/yyyy',
f8 DATE 'mm/dd/yyyy'

SAMPLE lines of the data file myfile.txt:

204,                                    ,        ,      ,      ,      ,      ,
205,                                    ,        ,      ,      ,      ,      ,
206,                                    ,        ,      ,      ,      ,      ,
207,                                    ,        ,      ,      ,      ,      ,

SqlLoaderLog file:
Record 1: Rejected - Error on table mytab, column f2.
ORA-01843: not a valid month

Open in new window

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

OP_ZaharinConnect With a Mentor Commented:
- i've tested the control file with sample of date with spaces (i manually press spacebar for the space), it seems able to load without error. can u try use trim() function on the date column.

f2 DATE "mm/dd/yyyy" nullif (f2=BLANKS) "TRIM(:f2)",


f2 DATE "mm/dd/yyyy" nullif f2=BLANKS "TRIM(:f2)",
- you can use nullif. following example is from memory:

f2 DATE 'mm/dd/yyyy' nullif (f2=BLANKS),


f2 DATE "mm/dd/yyyy" nullif f2=BLANKS,
hi tooki,
- i've tested and following is the working syntax. you need to do the same with the other date column:

f2 DATE "mm/dd/yyyy" nullif (f2=BLANKS),
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

toookiAuthor Commented:
Many thanks OP_Zaharin .
But it is not working. I get the same error.
Record 1: Rejected - Error on table mytab, column f2.
ORA-01843: not a valid month

No record gets loaded. This is probably because of space characters instead of BLANKS for that line in the data file.. Trying to troubleshoot..
toookiAuthor Commented:
Many thanks.

I tried but that did not work. Maybe somewhere some data is not in correct format in th ehuge table.
So I gave up and tried this way. I made the field varchar2. And it worked with the control file.

Then I created another table to read the varchar2 and converted to DATE. It is less difficult to convert when inside the oracle table.

- no problem toooki. yes i agree that is another way of handling the data when using sqlloader. there will be times i create a temp table, using sqlloader to load the data from text file into the temp table then do data cleansing in the temp table form rather that managing it thru sqlloader control file.  after the data is confirmed then i moved the data into the actual table. it might looks too many steps but the important is the data integrity is handled properly.

All Courses

From novice to tech pro — start learning today.