This content has been marked as final. Show 17 replies
NLS_NCHAR_CHARACTERSET is only used to store data in the NCHAR, NVARCHAR2 and NCLOB datatypes - does your database use these datatypes ?
Pl post the SQL that you use in TOAD to determine that you are viewing the data in the correct format.
I believe the issue is due to other issues
Hi, Thanks for your response.
The data with Brazilian characters is stored in a NCLOB datatype.
I am 100% sure that the problem is due to the NLS_NCHAR_CHARACTERSET is set to WE8ISO8859P1.
I confirmed that, becouse I created in my notebook 2 Oracle9i instances:
one of each with NLS_NCHAR_CHARACTERSET = WE8ISO8859P1
one of each with NLS_NCHAR_CHARACTERSET = AL16UTF16
The problem with the Brasilian characters (for example "ç á é í ó ú, etc) only happen with the Oracle9i with NLS_NCHAR_CHARACTERSET = WE8ISO8859P1.
My problem is becouse I don´t want to remove the customer database with with NLS_NCHAR_CHARACTERSET = WE8ISO8859P1 and create it again with NLS_NCHAR_CHARACTERSET = AL16UTF16 (is a big database).
I just want to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16.
Is that possible?
Thanks in advance
Thanks for your confirmation. The only way to change this is to recreate the database with the correct NLS_NCHAR_CHARACTERSET setting. Your NCLOB data in your database with WE8ISO8859P1 setting is likely corrupted and beyond repair.
Serguisz from Oracle (responder in this forum) may have better options for you
Thanks for your response again.!!
Just to confirm.
I made a mistake when tell you that the datatype is NCLOB.
In fact, the datatype is CLOB as you can see in the following link.
Is the same situation?, I must to recreate the database?
Another questions regarding this:
1 - if I recreate the database changing NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16, could I have problems with others application that are running against this database?, or do you believe that the behaviour of the database will not change ?
2 - Could I use EXP and IMP to export all database schemas / data, and import it again to the new database with NLS_NCHAR_CHARACTERSET=AL16UTF16 ?
Thanks in advance
Edited by: 880676 on Jun 30, 2012 8:14 AM
As I stated above, NLS_NCHAR_CHARACTERSET is only used to store data in the NCHAR, NVARCHAR2 and NCLOB datatypes - if you are not using these datatypes, then your issue lies elsewhere.
NLS_CHARACTERSET values of WE8ISO8859P1 and WE8MSWIN1252 are both capable of storing Portuguese.
Pl identify the exact steps and tools you are using to determine that the data is "corrupted". Pl confirm that when you view the data thru SQL Developer, it shows as "corrupted"
After reading all I understand that:
The NLS_CHARACTERSET is used for CHAR, VARCHAR2, and CLOB columns;
The NLS_NCHAR_CHARACTERSET is used for NCHAR, NVARCHAR2 and NCLOB columns.
I am using "CLOB" to store data with Brasilian characters.
So, the "CORRECT" question is: (sorry)
Can I change the NLS_CHARACTERSET from WE8ISO8859P1 TO WE8MSWIN1252 without to recreate the database ?
See the following link https://www.dropbox.com/s/1otqkbg096mg4tb/Oracle%20Settings2.PNG
Thanks in advance,
You can change WE8ISO8859P1 to WE8MSWIN1252 with the ALTER DATABASE CHARACTER SET command (Oracle9i only) but this is not the reason for your problem.
"But I built a xHarbour program to access it and the accentuation appears bad in the screen (<b>dos windows</b>) (crazy characters appears)."
Programs that run in a DOS Window (actually, Command Prompt window) need an individually set NLS_LANG value. In your DOS Window, issue the command CHCP
If the returned value is 850, do the following:
C:\>chcp Active code page: 850
If the returned value is 437, replace WE8PC850 with US8PC437.
C:\>set NLS_LANG=BRAZILIAN PORTUGUESE_BRAZIL.WE8PC850 C:\>yourprogram
The test with your own database created a pass-through scenario and the data was displayed correctly but not stored correctly. Use the UPPER function on some characters and you will see that they do not uppercase correctly.
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET WE8MSWIN1252;
The Brazilian characters (acentuation) appears fine from all my client programs.
Thanks so much.
Please, read my answer carefully! The change from WE8ISO8859P1 to WE8MSWIN1252 is a good idea by itself, as it solves potential issues with smart quotes or Euro sign, but it is not the solution to your problem with accented Portuguese letters! For Command Prompt programs you must set NLS_LANG as in my previous answer. Do not get fooled by the fact that data looks correctly. Enter some lowercase letters and then run UPDATE table SET column = UPPER(column). You will see that the letters (at least some of them) are not uppercased correctly, unless you set NLS_LANG as required.
Thanks for your interest in this matter.
But believe in me, the problemas was solved. !!
Five people was tested all the programs for hours today, retrieving data from the database, inserting all kind of Brazilian characters in new records, updating it a lot of times using lowercase and uppercase, and all the Braziian characters with acentuation and every other special character was tested one by one.
My customer works with very critical and official documents, therefore needs this kind of characters in the screen and to print in paper. All is working fine now.
Of course before to change the NLS_CHARCHARACTERSET from WE8ISO8859P1 to WE8MSWIN1252 I tested all the NLS_LANG sets, including the NSL_LANG sugguested by you, without success. (I spent 3 days in this week to research this matter, and before to place a post here, I was already tested all the NLS_LANG sets possible.
Don´t worry, the problem "is solved" only changing the NLS_CHARCHARACTERSET from WE8ISO8859P1 to WE8MSWIN1252.
Thanks you for the ALTER DATABASE sintax.
If your application works correctly with WE8MSWIN1252, then either it is really a GUI application (just started from Command Prompt) or it does internal code page switching. This is rare, but possible. On the other hand, if your application works with WE8MSWIN1252 but it did not work with WE8ISO8859P1, then the problem was not with Portuguese accented letters but rather with the Euro currency symbol, non-ASCII single- and double-quote characters, or the TM symbol. Portuguese accented letters have exactly the same binary codes in WE8MSWIN1252 and WE8ISO8859P1, so unless your application framework does some very unexpected things based directly on the database character set declaration, there is no way the handling of Portuguese letters would differ.