• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6241
  • Last Modified:

SQL Loader and UTF-8 character set conversion errors

I have recently upgraded my 9i database to the UTF-8 character set.  Now when I try to use SQL*Loader to load a text file containing non-standard symbols such as copyright ©, trademark ™, etc. they do not load correctly into the database.  I can see them in notepad before loading but when I try to view them in SQLPlus, all I see is squares and question marks.  My database is on a Solaris box and I am running the load from there.
0
eric72871
Asked:
eric72871
1 Solution
 
schwertnerCommented:
SQL*Loader like Export and Import uses the environment variable NLS_LANG to encounter the character set of the target DB.
If you are using AL32UTF8 as char. set on the db set
NLS_LANG=.UTF8
as environment variable of the 'oracle' user.
0
 
eric72871Author Commented:
The locale of the session was:
$ locale
LANG=
LC_CTYPE="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_COLLATE="C"
LC_MONETARY="C"
LC_MESSAGES="C"
LC_ALL=

I changed it with the following:
$ LANG=en_US
$ LC_ALL=en_US.UTF-8
$ export LC_ALL LANG

$ locale
LANG=en_US
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_ALL=en_US.UTF-8

and added the NLS_LANG parameter NLS_LANG=AMERICAN_AMERICA.UTF8

This still did not work.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
NievergeltCommented:
I suspect that the text you try to load is plain ISO Latin-1 or Windows 8-bit character set.

But if you set NLS_LANG to AMERICAN_AMERICA.UTF8, you tell the loader that its UTF8, which means that only the ASCII (7-bit) part of the characters is represented by only one character.

So what you have to set NLS_LANG to, is the actual character set of the text you want to load, e.g AMERICAN_AMERICA.WE8ISO8859P1 or AMERICAN_AMERICA.WE8MSWIN1252.

This should help you overcome your problems.

Share and Enjoy  Christoph

0
 
MetanilCommented:
I think this will solve your problem..
I too have the same problem before..

u set the NLS_LANG in your database instance right???
like NLS_LANG=AMERICAN_AMERICA.UTF8

but this is not enough for SQL Loader..

try exporting NLS_LANG in your shell where sqlldr is to execute like

$export NLS_LANG=AMERICAN_AMERICA.UTF8

now NLS_LANG will be effective to sqlloader..

Note: AMERICAN_AMERICA.UTF8 (i just assume this, this value may be other according to your type)
0
 
riazpkCommented:
and if you are using Windows, then nls_lang variable can be found in hkey_local_machine\software\oracle
0
 
eric72871Author Commented:
Christoph,
I did not realize the Windows had it's own character set (WE8MSWIN1252).  I had tried set the NLS_LANG to WE8ISO8859P1 but it was not working.  Once I added the correct character set, the data loaded just fine.

Thanks for everyones help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now