This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Dec 14, 2012 8:49 AM by 905188 RSS

Special characters issue

905188 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    Please use SQL Developer, because , is have UNICODE support, TOAD id not support Unicode.

    Mahir
  • 6. Re: Special characters issue
    chris227 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    [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 Expert
    Currently Being Moderated
    [Edit: sorry, wanted to reply to OP]

    Edited by: Stew Ashton on Dec 14, 2012 3:40 PM
  • 12. Re: Special characters issue
    chris227 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Hi,

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points