Saving Persian/Arabic Digits and Numbers inside Oracle Database

Question

We have an Oracle Database which has many records in it. Recently we noticed that we can not save Persian/Arabic digits within a column with a datatype nvarchar2 and instead of the numbers it shows question marks "?".

I went through this to check the charset using these commands :

SELECT * 
from NLS_DATABASE_PARAMETERS 
WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

and this command

SELECT USERENV('language') FROM DUAL;

The results are these two respectively:

I also issue this command :

SELECT DUMP(myColumn, 1016) FROM myTable;

And the result is like this :

Typ=1 Len=22 CharacterSet=AL16UTF16: 6,33,6,44,6,27,6,45,0,20,0,3f,0,3f,0,2f,0,3f,0,2f,0,3f

The results seem to be okay but unfortunately we still cannot save any Persian/Arabic digit within that column. however the Persian/Arabic alphabets are okay. Do you know what is the cause of this problem ?

Thank You

What do you get when you run `SELECT DUMP(..., 1016) FROM ...?` — Wernfried Domscheit, Dec 21 '17 at 10:04
If you're seeing this in SQL Developer (as that seems to be where you're querying those settings), what encoding are you using - from Tools->Preferences->Environment If you aren't seeing it there, then where do you see it? — Alex Poole, Dec 21 '17 at 10:18
@WernfriedDomscheit Here is the result https://imgur.com/a/y64CM — Mehdi, Dec 21 '17 at 11:27
Please don't paste screenshots. Edit your question with result as text. — Wernfried Domscheit, Dec 21 '17 at 11:48
@WernfriedDomscheit I did the thing you said and I edited the question and put it there — Mehdi, Dec 21 '17 at 13:33
Again, don't paste pictures/screen shots. Add result as text! And you must select the column, not from a string. Character set is `AR8MSWIN1256` - obviously you did not select a `NVARCHAR2` column. It should be `AL16UTF16` — Wernfried Domscheit, Dec 21 '17 at 14:18
Finally we managed to make the first step to find a solution. `0,3f` is the question mark, so it is not a simple display problem - you have wrong data in your database (which cannot be corrected anymore, the information is lost). Most likely it was inserted due to wrong settings of your client application. Since you don't tell us which application you are using it's almost impossible to provide any help. — Wernfried Domscheit, Dec 21 '17 at 15:12
@WernfriedDomscheit I already told you already that I'm using Java JDBC application for that. It's a thin JDBC driver in the weblogic and we have connected to the database using data source. I'm trying to solve the problem using this URL https://docs.oracle.com/middleware/1221/wls/JDBCA/third_party_drivers.htm#JDBCA234 , Globalization Support for the Oracle Thin Driver section — Mehdi, Dec 21 '17 at 15:59
did your issue fixed @Mehdi I face the same issue and cannot find the answer please add your answer if you find a solution thanks — FarOoOosa, May 29 '18 at 07:49

Wernfried Domscheit · Answer 1 · 2017-12-21T10:07:07.567

USERENV('language') does not return your client characters set.

So, SELECT USERENV('language') FROM DUAL; is equal to

SELECT l.value||'_'||t.value||'.'||c.value
FROM (SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_LANGUAGE') l
    CROSS JOIN (SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_TERRITORY') t
    CROSS JOIN (SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET') c;

It is not possible to get the client NLS_LANG by any SQL statement (although there seems to be a quirky workaround: How do I check the NLS_LANG of the client?)

Check your client NLS_LANG setting. It is defined either by Registry (HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG, resp. HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG) or as Environment variable. The Environment variable takes precedence.

Then you must ensure that your client application (you did not tell us which one you are using) uses the same character set as specified in NLS_LANG.

In case your application runs on Java have a look at this: Database JDBC Developer's Guide - Globalization Support

See also OdbcConnection returning Chinese Characters as "?"

Actually our application has been written in Java. I'm trying to check the JDBC Globalization link you sent — Mehdi, Dec 21 '17 at 11:30

score 0 · Answer 2 · answered Dec 21 '17 at 16:08

0

Do yourself a favor and convert the main character set of your database from AR8MSWIN1256 to AL32UTF8. Most of these problems will simply go away. You can forget about NCHAR and NVARCHAR2. They are not needed anymore.

It's a one-time effort that will pay back a thounsand times.

See Character Set Migration for instructions.

answered Dec 21 '17 at 16:08

Codo

75,595
17
168
206

what will happen to my data if I do this ? – Mehdi Dec 21 '17 at 16:12
All data that was saved as AR8MSWIN1256 will be properly converted to AL32UTF8. And as it is a superset of AR8MSWIN1256, the conversion is lossless. – Codo Dec 21 '17 at 16:21
I will try this solution and I will let you know Thank You Codo – Mehdi Dec 21 '17 at 16:23
But according to DUMP results you have wrong data in the database. Of course they will remain wrong. – Wernfried Domscheit Dec 21 '17 at 16:25
1

Yes, the conversion cannot magically restore the question marks. The dump shows that the question mark is stored in the database and not an artifact of displaying invalid data. – Codo Dec 21 '17 at 16:30
@WernfriedDomscheit I know, since they've been stored wrongly, it's okay – Mehdi Dec 21 '17 at 16:31
1

Converting the DB to AL32UTF8 is certainly a good idea idea, however it will not solve your problem unless you configure your weblogic/java properly. Since you have a NVARCHAR2 column (AL16UTF16) is hss full unicode support anyway. – Wernfried Domscheit Dec 21 '17 at 18:16

Saving Persian/Arabic Digits and Numbers inside Oracle Database

2 Answers2