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


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


It will fail, as it expect data like this:

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

how can I load this data?

  • What Oracle version? – Philᵀᴹ Jun 14 '16 at 8:06
  • @Phil oracle version is 11g – WestFarmer Jun 14 '16 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 '16 at 21:27

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 ??

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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