Home » SQL & PL/SQL » SQL & PL/SQL » character translation (oracle 11.2.0.2 solaris 2.10)
character translation [message #562367] Tue, 31 July 2012 15:13 Go to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
Can some tell me what I need to do to avoid character translation?
Note the character 'Ã' was translated into a '?'

Thanks to all who answer



SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

VALUE$
-----------------------------------------
WE8ISO8859P1

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.2.0

20 rows selected.




SQL> insert into xxx values ('MagalhÃ.£es Jr');

1 row created.

SQL> commit;

Commit complete.


SQL> select * from xxx;

A
--------------------
Magalh?.??es Jr

Re: character translation [message #562369 is a reply to message #562367] Tue, 31 July 2012 16:59 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
Is problem one of data storage or date presentation?

SELECT ASCIISTR(COL1) FROM XXX;

post results from SQL above.
Re: character translation [message #562370 is a reply to message #562369] Tue, 31 July 2012 18:07 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member


SQL> SELECT ASCIISTR(A) from xxx;

ASCIISTR(A)
-----------------------------------
Magalh\00BF.\00BF\00BFes Jr


SQL> select dump(a,16) from xxx;

DUMP(A,16)
----------------------------------
Typ=1 Len=15: 4d,61,67,61,6c,68,bf,2e,bf,bf,65,73,20,4a,72


Re: character translation [message #562371 is a reply to message #562370] Tue, 31 July 2012 18:12 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
It appears that sqlplus is unable to properly present or display the byte value of "BF"; decimal 191.
Re: character translation [message #562376 is a reply to message #562370] Wed, 01 August 2012 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In WE8ISO8859P1, character of code BF is euro sign, code point of character à is C3 (195), so you have a mismatch somewhere.

Post the result of:
DEFINE _SQLPLUS_RELEASE
select distinct CLIENT_CHARSET from v$session_connect_info where CLIENT_CHARSET is not null;

Regards
Michel

character translation [message #562472 is a reply to message #562376] Wed, 01 August 2012 10:41 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member

SQL> DEFINE _SQLPLUS_RELEASE

DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR)

SQL> select distinct CLIENT_CHARSET from v$session_connect_info where CLIENT_CHARSET is not null;

CLIENT_CHARSET
-----------------------------------------
AL32UTF8

I did the following before launcing SQLPLUS and it appears that
I am getting closer but still not an exact match as to what my insert statement was above.

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8


Note the upside down quetion marks '¿' after the 'Â'


SQL> select * from xxx;

A
------------------------------------------------------------
Magalh¿.¿¿es Jr


insert into xxx values ('MagalhÃ.£es Jr');



I think the answer lies in the export statment but how do you know
what the correct values should be?

Re: character translation [message #562475 is a reply to message #562472] Wed, 01 August 2012 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are sure your client OS works with AL32UTF8 character set?
Also post
select NETWORK_SERVICE_BANNER from v$session_connect_info where lower(NETWORK_SERVICE_BANNER) not like '%unknown%';


Regards
Michel
Re: character translation [message #562517 is a reply to message #562367] Wed, 01 August 2012 18:21 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member

SQL> select NETWORK_SERVICE_BANNER from v$session_connect_info where lower(NETWORK_SERVICE_BANNER) not like '%unknown%';


NETWORK_SERVICE_BANNER
--------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.2.0 - Pro
duction

Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
2.0 - Production

Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
Oracle Advanced Security: authentication service for Linux: Version 11.2.0.2.0 -
 Production


NETWORK_SERVICE_BANNER
--------------------------------------------------------------------------------
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.2.0 - Pro
duction

Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
2.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.2.0 - Pro
duction

Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.

NETWORK_SERVICE_BANNER
--------------------------------------------------------------------------------
2.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.2.0 - Pro
duction

Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
2.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
Oracle Advanced Security: encryption service for Linux: Version 11.2.0.2.0 - Pro

NETWORK_SERVICE_BANNER
--------------------------------------------------------------------------------
duction

Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.
2.0 - Production


16 rows selected.

Re: character translation [message #562528 is a reply to message #562517] Thu, 02 August 2012 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you are on Linux (I advice you to use "set pagesize 1000" in SQL*Plus).
Post the result of "locale".

Regards
Michel
Re: character translation [message #562606 is a reply to message #562528] Thu, 02 August 2012 13:15 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
Not to sure what you mean by "locale"? Is there A query to
run to get these values? If can you provide it?
Re: character translation [message #562611 is a reply to message #562606] Thu, 02 August 2012 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"locale" is a command you have to type at OS command prompt.

Regards
Michel
Re: character translation [message #562615 is a reply to message #562611] Thu, 02 August 2012 15:25 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member

locale
LANG=C
LC_CTYPE="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_COLLATE="C"
LC_MONETARY="C"
LC_MESSAGES="C"
LC_PAPER="C"
LC_NAME="C"
LC_ADDRESS="C"
LC_TELEPHONE="C"
LC_MEASUREMENT="C"
LC_IDENTIFICATION="C"
LC_ALL=

Re: character translation [message #562616 is a reply to message #562615] Thu, 02 August 2012 15:47 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
bcm@bcm-laptop:~$ locale
LANG=en_US.utf8
LANGUAGE=
LC_CTYPE="en_US.utf8"
LC_NUMERIC="en_US.utf8"
LC_TIME="en_US.utf8"
LC_COLLATE="en_US.utf8"
LC_MONETARY="en_US.utf8"
LC_MESSAGES="en_US.utf8"
LC_PAPER="en_US.utf8"
LC_NAME="en_US.utf8"
LC_ADDRESS="en_US.utf8"
LC_TELEPHONE="en_US.utf8"
LC_MEASUREMENT="en_US.utf8"
LC_IDENTIFICATION="en_US.utf8"
LC_ALL=
Re: character translation [message #562618 is a reply to message #562616] Thu, 02 August 2012 17:10 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
Are you suggesting I set these values to 'en_US.utf8"
Re: character translation [message #562639 is a reply to message #562618] Fri, 03 August 2012 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your environment is not set for UTF8 as you told Oracle it is.
Set NLS_LANG with the correct character set you use in your terminal or set your terminal to use UTF8.

Regards
Michel
Re: character translation [message #562723 is a reply to message #562639] Fri, 03 August 2012 17:59 Go to previous messageGo to next message
raceone
Messages: 3
Registered: July 2012
Junior Member
you should set your character to UTF8 on your data base sql or what ever is your data base
Re: character translation [message #562724 is a reply to message #562723] Fri, 03 August 2012 18:04 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
>you should set your character to UTF8 on your data base sql or what ever is your data base
OK, how do you set "your character to UTF8"?
Please post reproducible test case that shows what you do & how Oracle RDBMS responds.
Re: character translation [message #562735 is a reply to message #562723] Sat, 04 August 2012 00:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
raceone wrote on Sat, 04 August 2012 00:59
you should set your character to UTF8 on your data base sql or what ever is your data base


And why should be set that? And how this will solve his problem?
Explain your "solution"

Regards
Michel

character translation [message #562759 is a reply to message #562639] Sat, 04 August 2012 12:04 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
obviously this is not correct


export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Is there a West European character set I can set this ENV variable too in order to avoid translattion?


Re: character translation [message #562760 is a reply to message #562759] Sat, 04 August 2012 12:15 Go to previous messageGo to next message
BlackSwan
Messages: 22780
Registered: January 2009
Senior Member
>Is there a West European character set I can set this ENV variable too in order to avoid translattion?

It appears that the root cause it at the OS level & does not involve the DB.
Do you understand the difference between a data storage problem & a data presentation issue?
Re: character translation [message #562761 is a reply to message #562759] Sat, 04 August 2012 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your NLS_LANG parameter should reflect what you use at OS level.
So what character set to you use at OS level?
It appears you do not set "locale".
What OS client program do you use?
With which character set is it configured?

Regards
Michel
Re: character translation [message #562763 is a reply to message #562761] Sat, 04 August 2012 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A quick search on the web directs me to this page:

Quote:
"C" - the standard value, usually the default, the same like not setting the category at all. 7-bit ASCII charset, no goodies.


So à is not supported.

Quote:
Now try "locale -a" to see the available values for the locale on your system.


So do it.

Quote:
A correct value for western-europe 8-bit likely "sounds" like "iso88591" or "en_US", because on numerous Unix systems I tried it was always one of the following:
iso_8859_1, en_US, en_US.iso88591, en_US.ISO8859-1, en_US.ISO_8859-1.


Which should be a good value as your database character set is WE8ISO8859P1 (but only if it is in the previous list).

Quote:
Look for manual pages like 'environ(5)/(7), locale(1)/(7)/(5), setlocale(3C)/(3), localedef(4), i18n_intro(5), l10n_intro(5),' etc, and find out about all the according environment variables, the most important ones being LC_ALL, LC_CTYPE, LC_MESSAGES and LANG.

Pay attention to chose the proper section, because there might be several entries with the same name. This means for example "man 5 environ" (or "man -s5 environ" on Solaris). The numbers in parentheses above are suggestions for sections in which you might find them. -- It's time to do "man man" now, if you didn't knew that by heart.


A good reading with many other information about character set on Unix.

Regards
Michel


[Updated on: Sat, 04 August 2012 13:06]

Report message to a moderator

character translation [message #562863 is a reply to message #562763] Mon, 06 August 2012 06:44 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
attached is the output of locale -a command. Do I need to go back to my UNIX
ADMIN and have them intall other charactersets to support the configuration in my DB?
  • Attachment: locale.log
    (Size: 6.76KB, Downloaded 74 times)
Re: character translation [message #562868 is a reply to message #562863] Mon, 06 August 2012 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, read what I said:

Quote:
Quote:
A correct value for western-europe 8-bit likely "sounds" like "iso88591" or "en_US", because on numerous Unix systems I tried it was always one of the following:
iso_8859_1, en_US, en_US.iso88591, en_US.ISO8859-1, en_US.ISO_8859-1.


Which should be a good value as your database character set is WE8ISO8859P1 (but only if it is in the previous list).


Regards
Michel
character translation [message #562921 is a reply to message #562868] Mon, 06 August 2012 17:35 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member


locale -a | grep en_US.iso88591
en_US.iso88591
en_US.iso885915





Can you please advise to the next step/(s)? Can I just export LANG=en_US.iso88591?
Re: character translation [message #562929 is a reply to message #562921] Tue, 07 August 2012 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59078
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the posts I wrote? Did you read the link I pointed you to?

Regards
Michel
Re: character translation [message #563755 is a reply to message #562929] Thu, 16 August 2012 15:14 Go to previous message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
My solution was to rebuild my DB with these values. Thanks to all who commented.


...
...
character set AL32UTF8
national character set AL16UTF16
...
...
Previous Topic: nullif and zero_divide
Next Topic: Inserts based on column values
Goto Forum:
  


Current Time: Tue Sep 16 03:10:04 CDT 2014

Total time taken to generate the page: 0.05579 seconds