SQL-LDR controlfile error, fixed width files

I can't seem to see my error:
this is from the log file...

Table SQRL_SALES_DETAIL, loaded when 37:40 != 0X39393939(character '9999')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CHECK_NUM 1:4 4 CHARACTER
DATA_DATE 5:10 6 DATE TO_DATE(:DATA_DATE, 'MM/DD/YY')
STORENUM 11:14 4 CHARACTER
EMPNUM 15:23 9 CHARACTER
EMPNAME 24:33 10 WHT CHARACTER
SEATNUM 34:36 3 CHARACTER
ITEMNUM 37:40 4 CHARACTER
ITEMDESC 41:56 16 WHT CHARACTER
FACTOR 57:62 6 CHARACTER
OPRICE 63:72 10 CHARACTER
SPRICE 73:82 10 CHARACTER
Record 1: Rejected - Error on table SQRL_SALES_DETAIL, column DATA_DATE.
ORA-00907: missing right parenthesis

This is my control file:
LOAD DATA
INFILE 'all_close_data.dat'
BADFILE 'all_close_data.bad'
DISCARDFILE 'all_close_data.discard'
APPEND
INTO TABLE SQRL_SALES_DETAIL
WHEN (37-40) != '9999'
TRAILING NULLCOLS
(CHECK_NUM POSITION(01:04) INTEGER EXTERNAL,
DATA_DATE POSITION(05:10) DATE "TO_DATE(:DATA_DATE, 'MM/DD/YY')",
STORENUM POSITION(11:14) INTEGER EXTERNAL,
EMPNUM POSITION(15:23) INTEGER EXTERNAL,
EMPNAME POSITION(24:33) CHAR TERMINATED BY WHITESPACE,
SEATNUM POSITION(34:36) INTEGER EXTERNAL,
ITEMNUM POSITION(37:40) INTEGER EXTERNAL,
ITEMDESC POSITION(41:56) CHAR TERMINATED BY WHITESPACE,
FACTOR POSITION(57:62) DECIMAL EXTERNAL,
OPRICE POSITION(63:72) DECIMAL EXTERNAL,
SPRICE POSITION(73:82) DECIMAL EXTERNAL
)

==========
what is wrong with my DATE formatter?
Note, the data for that col looks like "10/31/00"

thanks

I haven't used sql loader much so this may be an incorrect observation, but I'll mention it anyway.

Your control file is checking positions 5-10 for the date, that's 6 characters.

Your date format is "mm/dd/yy" which is 8 characters.

Unless sql loader ignores the '/' or something else I'm unaware of, it's only seeing "mm/dd/" when it try's to make the date. I'm not sure why that would generate a missing right paren error though, maybe I'm missing something :)

thanks. I knew it was something simple... I was just staring at it too long.

B.

i fixed that... made it 'MMDDYY' but I still get the Missing Right Parenthesis" error.

so back to square 1...

Try this out,

DATA_DATE POSITION(05:10) DATE "MM/DD/YY",


I hope this would work for you.

Sam

I would have agree with "sambavan". If you set up your control card with his fix you should be o.k.

Let us know.

YES... thanks a lot. I guess I was trying to change the data where for dates you only need to show the format.

There isn't an example for this in the Oracle docs.

thanks all.