0

I want to load data file which contains date fields in this format:

10-AUG-16

but, my oracle NLS_LANG is "Simplified Chinese_China.ZH16GBK", if I specify date format in control file like this:

dd-Month-YY

It will fail, as it expect data like this:

10-八月 -16 // 八月 means august in Chinese

how can I load this data?

3
  • What Oracle version?
    – Philᵀᴹ
    Jun 14, 2016 at 8:06
  • @Phil oracle version is 11g
    – WestFarmer
    Jun 14, 2016 at 8:49
  • I'm not sure how the language will factor in, but from a pure English standpoint, your data format mask does not match your data. If your data is '10-Aug-16', then your mask would be 'dd-Mon-yy', not 'dd-Month-yy'. That's assuming the '10' really is the day. And it's long past time to lose the 2-digit years. Legions of guys like me busted our butts to fix that 16 years ago. Don't repeat the mistakes that lead to Y2K.
    – EdStevens
    Jun 14, 2016 at 21:27

1 Answer 1

2

TO_DATE functions accepts the NLS language as an optional parameter .. have you tried pulling it in via SQL*Loader as a CHAR, then do a TO_DATE conversion on it inline?

so your SQL*loader syntax might look like:

  ...
    my_date     CHAR  "TO_DATE(:my_date, 'dd-MON-RR', 'NLS_DATE_LANGUAGE=American')"
  ...

See if that works any better ??

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.