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.
eric72871Asked:
Who is Participating?
 
NievergeltConnect With a Mentor Senior SW DevCommented:
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
 
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.