SQLLDR - ORA-01843 on NULL dates 1

[RottPaws]

I've searched "ask Tom", tek-tips, and just about everything else google pointed me to, but I haven't been able to find anything to resolve my problem.

I'm using Oracle 9i on a RH Linux 9 box.

I get a status report from a vendor and save it as a csv which I then try to load into Oracle. It works fine unless there is a blank date in a record.

I'm using both TRAILING NULLCOLS and NULLIF, but it is still failing.

Here's my ctl file:

load data
infile '/home/l3/cfr.csv'
badfile '/home/l3/cfr.bad'
truncate
into table L3_CFR
fields terminated by ","
TRAILING NULLCOLS
(ORDER_ID,
 L3_ORDER_ID,
 REQ_SUBMIT_DT DATE "MM/DD/YY",
 L3_RX_DATE DATE "MM/DD/YY",
 REQ_TYPE CHAR(17),
 TN_TYPE CHAR(7),
 NPA CHAR(3),
 NXX CHAR(3),
 DNIS CHAR(4),
 L3_STATUS CHAR(15),
 L3_JEOP_CD CHAR(10),
 L3_STATUS_DT DATE "MM/DD/YY",
 E911_STATUS CHAR(15),
 E911_JEOP_CD CHAR(10),
 E911_STATUS_DT DATE "MM/DD/YY" NULLIF (E911_STATUS_DT=BLANKS)
)

And here are a few rows from my data file:

53477777,3360459,3/24/05,3/24/05,New,Primary,234,567,8901,Completed,,3/25/05,Completed,,4/05/05
53378888,3360370,3/23/05,3/23/05,New,Primary,345,678,9012,Completed,,3/25/05,Completed,,4/05/05
53378888,3360410,3/23/05,3/23/05,New,Primary,345,567,9013,Completed,,3/25/05,Completed,,4/05/05
54119999,,4/08/05,4/13/05,New,Primary,123,456,7890,Completed,,4/11/05,Pending,,
54119999,,4/08/05,4/13/05,New,Primary,123,456,7891,Completed,,4/11/05,Pending,,
54119999,,4/08/05,4/13/05,New,Primary,123,456,7892,Completed,,4/11/05,Pending,,
54119999,,4/08/05,4/13/05,New,Primary,123,456,7893,Completed,,4/11/05,Pending,,

The load fails for the rows with no date in the last column. Here's what I get in the log file:

Record 4: Rejected - Error on table L3_CFR, column E911_STATUS_DT.
ORA-01843: not a valid month

Record 5: Rejected - Error on table L3_CFR, column E911_STATUS_DT.
ORA-01843: not a valid month

Record 6: Rejected - Error on table L3_CFR, column E911_STATUS_DT.
ORA-01843: not a valid month

Record 7: Rejected - Error on table L3_CFR, column E911_STATUS_DT.
ORA-01843: not a valid month

Any ideas?

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[hoinz]

Not sure about this,
but can you verify what is at the end of the lines?
Maybe there is a Dos-like CR-LF instead of a Unix-like LF?

 

[LKBrwnDBA]

1) You could try removing the "NULLIF..."
2) And/or try this:

...
E911_STATUS_DT "TO_DATE(:E911_STATUS_DT,'MM/DD/YY')" NULLIF (E911_STATUS_DT=BLANKS)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[RottPaws]

Resaving the file in a unix format worked. That idea hadn't occurred to me because it worked find as long as the final date field wasn't blank.

I tried the TO_DATE method, but I couldn't get it to work. I kept getting "date format not recognized" errors.

So I'll just have to make my script look at the file and make sure it uses unix-type new-lines.

Thanks a bunch!

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[oradba101]

I use a book from O'Reilly called "SQL*Loader - the Definitive Reference (I believe is the title). I don't have it here with me but I found it to be an invaluable resource when it comes to using SQL*Loader.

HTH,



William Chadbourne
Oracle DBA
TCMHS

 

[RottPaws]

Thanks for the tip.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!