1 2 Previous Next 17 Replies Latest reply: Jul 24, 2012 7:34 PM by 883679 RSS

    How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?

    883679
      [https://www.dropbox.com/s/cqsbioq5nwishfo/Oracle%20Settings.PNG]

      Hi,

      My customer have an Oracle9i. (in Brazil).

      I already loaded all the data that I need, and I have problem with the Brazilian character accentuation.

      The data with accentuation in the database are stored correctly, (I can see it fine using a SELECT manually in SQLPLUS or TOAD)

      But I built a xHarbour program to access it and the accentuation appears bad in the screen (dos windows) (crazy characters appears).

      I already was created another Oracle9i database in my notebook and I can see the characters with accentuation fine, therefore I have only accentuation problems when run my xHarbour program against the database of mu customer.

      I noticed that the NLS_NCHAR_CHARACTERSET in the database of my customer is set to WE8ISO8859P1.

      I my local Oracle9i Database, the NLS_NCHAR_CHARACTERSET is set to AL16UTF16

      I believe this is the problem.!!

      My question is:

      How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 in Oracle9i ?

      Pay attention that my customer have 3 others Oracle9i Servers (Windows Server 2003) configured with NLS_NCHAR_CHARACTERSET=AL16UTF16, and working against this servers I can see the Brasilian characters "fine" from the xHarbour client program. (xHabour is similiar to the Clipper lenguage with functions to connect to Oracle)

      I have problems with Brazilian characters when working "only" against the server configured with NLS_NCHAR_CHARACTERSET = WE8ISO8859P1 (My customer have just one Oracle9i Server configured with NLS_NCHAR_CHARACTERSET = WE8ISO8859P1.

      All the other Oracle9i servers are configured with NLS_NCHAR_CHARACTERSET = AL16UTF16 (since the database creation), and I have not problems with this servers.

      So, my question is:

      How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 in Oracle9i ?

      I believe is something like ALTER SESSION, ALTER DATABASE, etc, but I am not sure. I want to make this change permanently.

      Could you help me with this, please?

      Please see the attached link.

      Thanks in advance,

      Luigggye
        • 1. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
          Srini Chavali-Oracle
          NLS_NCHAR_CHARACTERSET is only used to store data in the NCHAR, NVARCHAR2 and NCLOB datatypes - does your database use these datatypes ?

          http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch7progrunicode.htm#NLSPG0071

          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

          HTH
          Srini
          • 2. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
            883679
            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

            Luigggye
            • 3. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
              Srini Chavali-Oracle
              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

              HTH
              Srini
              • 4. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                883679
                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.

                http://www.dropbox.com/s/cv61r5phf81q5oc/CLOB%20Datatype.PNG

                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

                Luigggye

                Edited by: 880676 on Jun 30, 2012 8:14 AM
                • 5. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                  Srini Chavali-Oracle
                  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"

                  HTH
                  Srini
                  • 6. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                    883679
                    OK

                    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,

                    L
                    • 7. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                      Sergiusz Wolicki-Oracle
                      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
                      C:\>chcp
                      Active code page: 850
                      If the returned value is 850, do the following:
                      C:\>set NLS_LANG=BRAZILIAN PORTUGUESE_BRAZIL.WE8PC850
                      C:\>yourprogram
                      If the returned value is 437, replace WE8PC850 with US8PC437.

                      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.


                      -- Sergiusz
                      • 8. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                        883679
                        FYI.

                        After this:

                        STARTUP MOUNT;
                        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;
                        SHUTDOWN IMMEDIATE;
                        STARTUP


                        The Brazilian characters (acentuation) appears fine from all my client programs.

                        Thanks so much.
                        • 9. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                          Sergiusz Wolicki-Oracle
                          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.


                          -- Sergiusz
                          • 10. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                            883679
                            Dear friend.

                            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.

                            L.
                            • 11. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                              Sergiusz Wolicki-Oracle
                              OK, then do one test more. Enter some Portuguese accented letters (Áá, Ââ, Ãã, Àà, Çç, Éé, Êê, Íí, Óó, Ôô, Õõ, Úú) through your application and then view them in Oracle SQL Developer. If you see them correctly there, then they are stored correctly. Otherwise, they are not.

                              -- Sergiusz
                              • 12. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                                883679
                                Done exactly as required by you and works fine as expected

                                Thanks again.

                                L
                                • 13. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                                  Srini Chavali-Oracle
                                  Agree with Sergiusz - something is not quite right here - issue being solved by simply switching to WE8MSWIN1252 does not make sense :-) Maybe we are not being given the complete picture or all of the details ?

                                  HTH
                                  Srini
                                  • 14. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
                                    Sergiusz Wolicki-Oracle
                                    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.


                                    -- Sergiusz
                                    1 2 Previous Next