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 (126.96.36.199 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.
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?
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.
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 188.8.131.52.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
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.