Sqlldr importing date/time problems

Hi Everyone,

I'm trying to load date/time data into a table. The data is formatted in one of two ways:
1. Exact Date/Time
2. A day start/end columns.

Oracle is rejecting all records that don't use the exact date/time format. I've specified Default NULL in the create table statement, but it seems to be ignored. What am I doing wrong?



--Create Table statement
CREATE TABLE TIMES
(
PRIME_ID NUMBER(17) not null,
A_SEQ_NO NUMBER(10),
STARTDATE DATE DEFAULT null,
ENDDATE DATE DEFAULT null,
DAY_START NUMBER(2),
DAY_END NUMBER(2)
);



--Control File and example data
unrecoverable load data
infile *
append
into table TIMES
(
PRIME_ID position (1:1) integer external,
A_SEQ_NO position (2:3) integer external,
STARTDATE position (5:23) date "yyyy-mm-dd hh24:mi:ss",
ENDDATE position (25:43) date "yyyy-mm-dd hh24:mi:ss",
DAY_START position (45:46) integer external,
DAY_END position (48:49) integer external
)

begindata
5 1 0 0
5 2 4 5
6 1 0 0
6 2 5 9
6 3 0 0
8 1985.01.01 00:00:00 2001.12.04 00:00:00
(EDIT COMMENT: The data is not being displayed correctly, but the data is in the correct position. I.E. No overlapping data.)

--Error Messages
Error Message:
Record 1: Rejected - Error on table T_RC_TIMES, column STARTDATE
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Record 2: Rejected - Error on table T_RC_TIMES, column STARTDATE
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


[Edited by pfc on 10-08-2001 at 04:53 AM]

since you specify the position of all your fields in your data, Oracle expects to find each line with the format :

PRIME_ID A_SEQ_NO STARTDATE ENDDATE DAY_START DAY_END

the fact that you have a NULL default value does not change anything to that, Oracle tries to insert your data, and expects a date in the 3rd and 4th fields, which is not the case ...

you have heterogeneous data, and what I would do would be to separate data in 2 files, create 2 temporary tables, one with PRIME_ID, A_SEQ_NO, STARTDATE, ENDDATE, and the other with the days instead of the dates, then you insert data in these 2 tables, and do an INSERT ... SELECT using NVLs to insert all your data in TIMES ...

try to make notice of the position
of each fields. And specally if the field can be NULL
the corresponding data can also be null. There might
be an issue of overlapping.

well, not just an issue of overlapping.
as what pipo says you have heterogeneous data.
You can also use the bad files to be loaded
again to another table. If there is necessary
to merge two table then you have to transform
data from one table to be uniform to another table.

Would a coma separated loader file have this problem, also?

I've only worked with fixed position data so I've never setup a variable length control file.

no, since a field with only dates would look like :

1,2,some date, some other date,,

and one with only days would look like :

1,2,,,3,4

no confusion is possible that way ...

In the interest of sharing solutions.

I found another solution to the heterogenous data.
Sqlldr is extremely powerful and I've found an option called NULLIF that will return a value of null if the condition is met so the contol file can be changed to this:

STARTDATE position (5:23) date "yyyy-mm-dd hh24:mi:ss" NULLIF STARTDATE=blanks,

ENDDATE position (25:43) date "yyyy-mm-dd hh24:mi:ss" NULLIF ENDDATE=blanks,

Enjoy. :)