This discussion is archived
4 Replies Latest reply: Jan 24, 2013 11:19 PM by Sergiusz Wolicki (Oracle) RSS

Stored arabic values in a database with an unsupported characterset

978413 Newbie
Currently Being Moderated
Hello,

We have our First Database (10.2.0.4 on unix platform) with IW8ISO8859P8 nls_characterset and AL16UTF16 nls_nchar_characterset,
which works with a certain application that can display values in arabic format.
The values are stored in a characterset which is not arabic supported in a varchar2 column type and the values are stored correctly in the database and shown correctly in the application.

We want to copy the arabic data to our Second Database (11.2.0.1 on Windows 2003 server) with IW8ISO8859P8 nls_characterset and AL16UTF16 nls_nchar_characterset, which works with Second application.
Therefore, we used Materialized View. Also, the arabic values are stored in the same characterset in our Second Database in a varchar2 column type.

After a test we did, we concluded that the values in the Materialzed View are correct because the ascii values stayed the same as the values in the First Database.

Our Second Application uses a table that gets the data from the Materialized View. It's important to indicate that the column in the table was a varchar2 type, but because our Second application couldn't show the arabic values correctly, we modified the column to be a nvarchar2 type (which is a best practice according to oracle).

After the modify, the ascii values did stay the same but we've got two problems:
1. Our Second application still cannot display arabic values.
2. When we load data from the Materialized View to the table of the Second Database, the ascii values are changing. We don't know whether its right or not.

Also, when we insert arabic values manually to the table of the Second Database, our Second application can display the values correctly. That happened of course, when the column type is nvarchar2 and not other type.

Hence,
We want to know what is the best practice for right conversion to nvarchar2 type, while the charactetset is not arabic supported. In addition, we also want to know how we can see correct arabic values in our Second application?
  • 1. Re: Stored arabic values in a database with an unsupported characterset
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    Oracle strong recommendation is not to cheat and not to use NAVRCHAR2. It is to create a database in AL32UTF8 and use it to store both Arabic and Hebrew and any other language you want.

    If you have a default configuration of the second database, then the NVARCHAR2 character set is AL16UTF16. It is different from IW8ISO8859P8, so the character codes change when transferred from VARCHAR2 to NVARCHAR2. As the codes are not stored correctly in VARCHAR2, the conversion yields incorrect AL16UTF16 codes and your second application, which is most probably correctly configured, cannot makes sense of this data. A trick that you can employ is to use the packages UTL_RAW and UTL_I18N to convert your data to RAW and then to NVARCHAR2. By specifying that data in RAW is in its real Arabic character set (you have to identify this character set first), you can ensure that the conversion to AL16UTF16 happens from the correct source character set.

    Saying the above, I still advise you to clean up this mess and make sure that the Arabic data is stored in Unicode AL32UTF8 (or at least a correct Arabic character set) and not in a Hebrew character set. Otherwise, you will have to keep cheating and finding workarounds each time you have to add some new stuff to your system.


    -- Sergiusz
  • 2. Re: Stored arabic values in a database with an unsupported characterset
    986671 Newbie
    Currently Being Moderated
    My System Param

    1     NLS_CSMIG_SCHEMA_VERSION     5
    2     NLS_LANGUAGE     AMERICAN
    3     NLS_TERRITORY     AMERICA
    4     NLS_CURRENCY     $
    5     NLS_ISO_CURRENCY     AMERICA
    6     NLS_NUMERIC_CHARACTERS     .,
    7     NLS_CHARACTERSET     WE8MSWIN1252
    8     NLS_CALENDAR     GREGORIAN
    9     NLS_DATE_FORMAT     DD-MON-RR
    10     NLS_DATE_LANGUAGE     AMERICAN
    11     NLS_SORT     BINARY
    12     NLS_TIME_FORMAT     HH.MI.SSXFF AM
    13     NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM
    14     NLS_TIME_TZ_FORMAT     HH.MI.SSXFF AM TZR
    15     NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
    16     NLS_DUAL_CURRENCY     $
    17     NLS_COMP     BINARY
    18     NLS_LENGTH_SEMANTICS     BYTE
    19     NLS_NCHAR_CONV_EXCP     FALSE
    20     NLS_NCHAR_CHARACTERSET     AL16UTF16
    21     NLS_RDBMS_VERSION     11.2.0.3.0
    22     NLS_DMU_USAGE     20121217172118,20121217140509,WE8MSWIN12



    Character Set is NLS_NCHAR_CHARACTERSET     AL16UTF16

    but still i couldn't save Arabic Characters in database..

    Used Unistr also

    update mst_code_language set display_value = unistr('ثقققثفغ')

    still my database column value is ??????. please let me know any possible ways to store arabic characters
  • 3. Re: Stored arabic values in a database with an unsupported characterset
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    >
    ...
    7 NLS_CHARACTERSET WE8MSWIN1252
    ...
    >

    AFAIK, WE8MSWIN1252 cannot store Arabic characters. Pl post a description of the table MST_CODE_LANGUAGE

    HTH
    Srini
  • 4. Re: Stored arabic values in a database with an unsupported characterset
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    UNISTR is used to enter character codes with the escape syntax \xxxx. If you want to use Arabic literals with NVARCHAR2 columns, you should prefix them with N, like N'ثقققثفغ', use a Unicode-capable client, e.g. written in Java, and enable the literal replacement feature.


    -- Sergiusz

Legend

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