CODEWITHSUNDEEP
oracle-databaseoracle11goracle10g
Mehdi
Mehdi

Reputation: 3763

Saving Persian/Arabic Digits and Numbers inside Oracle Database

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: enter image description here

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

Upvotes: 2

Views: 1584

Answers (2)

Codo
Codo

Reputation: 78835

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.

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

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 "?"

Upvotes: 1

Related Questions