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 (184.108.40.206 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.
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.