-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|