1 2 Previous Next 15 Replies Latest reply: Dec 14, 2012 10:49 AM by 905188 RSS

    Special characters issue

    905188
      Hello

      I'm facing an issue when inserting or updating data with the following special characters :

      -     CZECH: ů, ř, č, š, ž, á, ň, ý, ť, ď
      -     SLOVAK: ť, ď č, š, ž, á, ň, ý, ô, ľ, ŕ
      -     POLISH: ą, ę, ł, ś, ć, ź, ż, ń
      -     NORWEGIAN: ø,æ, å.

      I would like to modify nvarchar2 column in this table :

      CREATE TABLE MY_SCHEMA.MY_TABLE
      (
      ID_MY_TABLE NUMBER(9) NOT NULL,
      LANGUE VARCHAR2(5 BYTE) NOT NULL,
      CREATION_DATE DATE DEFAULT sysdate NOT NULL,
      DATE_FIN DATE,
      USER_ID NUMBER,
      DESCRIPTION NVARCHAR2(30),
      )

      Update statement is :

      update MY_SCHEMA.MY_TABLE m
      set m.description = *'Sličnaďźk'*
      where m.user_id = 1 and m.langue = 'nn-NO';

      Result (select) is : (special characters are not inserted)

      ID_MY_TABLE LANGUE CREATION_DATE DATE_FIN USER_ID
      ------------------- ------ ------------- -------- ----------
      DESCRIPTION
      ------------------------------ ------------------------------
      1     nn-NO     06/12/12          5074     Slicna     Slicnadzk

      Database is configured as below :
      select * from nls_database_parameters ;
      PARAMETER VALUE
      ------------------------------ ----------------------------------------
      NLS_LANGUAGE AMERICAN
      NLS_TERRITORY AMERICA
      NLS_CURRENCY $
      NLS_ISO_CURRENCY AMERICA
      NLS_NUMERIC_CHARACTERS .,
      NLS_CHARACTERSET WE8MSWIN1252
      NLS_CALENDAR GREGORIAN
      NLS_DATE_FORMAT DD-MON-RR
      NLS_DATE_LANGUAGE AMERICAN
      NLS_SORT BINARY
      NLS_TIME_FORMAT HH.MI.SSXFF AM
      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


      I tried with Toad and Oracle SQL Developer.

      Thanks beforehand for any help.

      Best regards,
        • 1. Re: Special characters issue
          ranit B
          Looks like a NLS/Globalization issue with Charsets.

          This being a Pl/Sql forum you might not get much inputs.
          Please try posting this here -- Globalization Support
          • 2. Re: Special characters issue
            chris227
            First of all you should ensure which bytes are in the database:
            select 
             dump(description, 1010)
            ,description
            from my_table
            
            could be something like
            
            DUMP(DESCRIPTION,1010) DESCRIPTION 
            Typ=1 Len=18 CharacterSet=AL16UTF16: 0,83,0,108,0,105,1,13,0,110,0,97,1,15,1,122,0,107 Sličnaďźk 
            From this we can go further.

            Edited by: chris227 on 14.12.2012 04:35
            result added
            • 3. Re: Special characters issue
              Stew Ashton
              Please consult this bit in the documentation:

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

              This seems to indicate it's a client side problem (and solution).

              See also

              http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#i42617

              especially where it says "To avoid potential loss of data during the text literal conversion to the database character set, set the environment variable ORA_NCHAR_LITERAL_REPLACE to TRUE. Doing so transparently replaces the n' internally and preserves the text literal for SQL processing."

              Don't forget to put the the n in there:
              set m.description = n'Sličnaďźk';
              • 4. Re: Special characters issue
                Veejays.User10302525-Oracle
                I tried to insert the value using sql developer and the value got inserted as well as is getting displayed properly. Sql Developer version 3.1.07
                create table mytable(DESCRIPTION NVARCHAR2(30));
                
                insert into mytable values('Sličnaďźk');
                
                commit;
                
                select * from mytable;
                
                "DESCRIPTION"
                "Sličnaďźk"
                
                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               AL32UTF8
                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.1.0
                
                SQL> select * from v$version;
                
                BANNER
                --------------------------------------------------------------------------------
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                PL/SQL Release 11.2.0.1.0 - Production
                CORE    11.2.0.1.0      Production
                TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
                NLSRTL Version 11.2.0.1.0 - Production
                • 5. Re: Special characters issue
                  Mahir M. Quluzade
                  Please use SQL Developer, because , is have UNICODE support, TOAD id not support Unicode.

                  Mahir
                  • 6. Re: Special characters issue
                    chris227
                    There is one, perhaps important difference: NLS_CHARACTERSET AL32UTF8
                    And we dont konw nothing yet about the clients NLS_LANG.
                    So i propose we first look at the bytes ended in the DB.
                    Then we look at the client.
                    • 7. Re: Special characters issue
                      Veejays.User10302525-Oracle
                      Chris,

                      I executed your query and below is the output.
                      select dump(description, 1010),description from mytable
                      
                      "DUMP(DESCRIPTION,1010)"|"DESCRIPTION"
                      "Typ=1 Len=18 CharacterSet=AL16UTF16: 0,83,0,108,0,105,1,13,0,110,0,97,1,15,1,122,0,107"|"Sličnaďźk"
                      • 8. Re: Special characters issue
                        905188
                        Hi user10302525,

                        Your NLS_CHARACTERSET parameter is AL32UTF8 ... so it's "normal" you're able to insert special characters unicode and have a correct display.

                        I cannot recreate database with AL32UTF8 charset but I should use NVARCHAR2 or NTEXT types.


                        Regards,
                        • 9. Re: Special characters issue
                          Veejays.User10302525-Oracle
                          Hi I have used NVARCHAR2(30 CHAR) for description column so AL32UTF8 would not apply but AL16UTF16 will apply. Howerver, NLS_LANG on my machine is AMERICAN_AMERICA.AL32UTF8
                          • 10. Re: Special characters issue
                            Stew Ashton
                            [Edit: I confused the original poster and someone who replied. You are all numbers to me :) ]

                            Edited by: Stew Ashton on Dec 14, 2012 3:42 PM
                            • 11. Re: Special characters issue
                              Stew Ashton
                              [Edit: sorry, wanted to reply to OP]

                              Edited by: Stew Ashton on Dec 14, 2012 3:40 PM
                              • 12. Re: Special characters issue
                                chris227
                                Hi,

                                that's somewhat surprising to me. I expected some kind of baseletter conversion.
                                I faced on 10.2.0.5 with WE8MSWIN1252 on Client and DB:
                                create table d( d nvarchar2(10))
                                insert into d values('č')
                                
                                select dump(d,1010),d from d
                                
                                DUMP(D,1010)     D
                                Typ=1 Len=2 CharacterSet=AL16UTF16: 0,99     c
                                But believe to i remeber that there have been some changes in 11.x.

                                So i have to go now, no tme anymore, but since the bytes in the DB are correct, it seems to be a client issue.
                                It must be confiigured accuratly to display the character in this encodig (UTF-16) correctly.
                                Perhaps best is to follow Stew advises or change to globalization forum (Here we are).

                                Edited by: chris227 on 14.12.2012 06:47
                                • 13. Re: Special characters issue
                                  905188
                                  I do exactly the same :

                                  create table mytable (description nvarchar2(30)) ;
                                  insert into mytable values(n'Sličnaďźk');
                                  commit;

                                  select dump(description,1010),description from mytable;

                                  Typ=1 Len=18 CharacterSet=AL16UTF16: 0,83,0,108,0,105,0,99,0,110,0,97,0,100,0,122,0,107     Slicnadzk


                                  select * from v$version;

                                  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                                  PL/SQL Release 11.2.0.2.0 - Production
                                  "CORE     11.2.0.2.0     Production"
                                  TNS for Linux: Version 11.2.0.2.0 - Production
                                  NLSRTL Version 11.2.0.2.0 - Production


                                  I'm using SQL Developer 3.1
                                  • 14. Re: Special characters issue
                                    chris227
                                    Hi,

                                    my last answer was to you, not to the other "number".
                                    Dont gorget to mention your client nls_Settings.
                                    1 2 Previous Next