0

I'm migrating a db from postgres to oracle.I create csv files with this command:

\copy ttt to 'C:\test\ttt.csv' CSV DELIMITER ',' HEADER encoding 'UTF8' quote as '"'; then with oracle sql loader I put data in oracle tables. It's all ok but I have in some description this character  that wasnt in the original DB. The encoding of db postgres is UTF8 and I'm on a window machine. Thanks to all. Gian Piero

Gian Piero
  • 75
  • 1
  • 12
  • Before you start sqlloader run `set NLS_LANG=.AL32UTF8` and `chcp 65001` – Wernfried Domscheit Jul 28 '17 at 14:53
  • Ok.It works.Thanks you.Two questions: 1)do you understand why there is this problem? 2) My sql loader is triggered by a java program Do you think it is enough to run this command from the command line from which you will launch java or should i run it from the program itself? Anyway I'm doing tests now – Gian Piero Jul 28 '17 at 15:13
  • Provided more information as answer. – Wernfried Domscheit Jul 28 '17 at 19:03

1 Answers1

1

Before you start sqlloader run 

chcp 65001    
set NLS_LANG=.AL32UTF8
  • chcp 65001 sets codepage of your cmd.exe to UTF-8 (which is inherited by sqlloader and sqlplus)
  • With set NLS_LANG=.AL32UTF8 you tell the Oracle database "The client uses UTF-8"

Without these commands you would have this situation (due to defaults)

chcp 850
set NLS_LANG=AMERICAN_AMERICA.US7ASCII

Maybe on your PC you have codepage 437 instead of 850, it depends whether your PC is U.S. or Europe, see National Language Support (NLS) API Reference, column OEM codepage

You can set NLS_LANG also as Environment Variable in PC settings or you can define it in Registry at HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 32 bit), resp. HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG

You can also change codepage of your cmd.ext persistent, see https://stackoverflow.com/a/33475373/3027266

For details about NLS_LANG see https://stackoverflow.com/a/33790600/3027266

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110