2

I am trying to insert Arabic characters in Oracle 11g, however the data is being inserted as question marks. I searched around and it seems i have to change the character encoding... But i am not sure how, and whether will it affect my database. Any help please ?

UPDATE LAM_INVESTMENT
SET INVESTMENT='ل'
WHERE KEY=11;

I tried updating from within SQL developer, also same outcome.

mikeb
  • 709
  • 2
  • 9
  • 35
  • Please Try my Answer here :- http://stackoverflow.com/questions/23610462/cant-insert-arabic-characters-into-oracle-database/42521619#42521619 – Michael Shenouda Mar 01 '17 at 01:48

3 Answers3

1

First of all check if your database is capable to store Arabic characters by

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

If you get values like AL32UTF8, AL16UTF16 or WE8ISO8859P6 you are fine and DB is able to store such characters.

In SQL Developer go to Tools / Preferences / Environment / Encoding and select UTF-8.

Set an Environment Variable to NLS_LANG=ARABIC_AMERICA.AL32UTF8 or similar, you can also do NLS_LANG=.AL32UTF8 in order to keep default/existing language and territory. Alternatively you can set is also in your Registry at HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 32 bit), resp. HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 64 bit).

Then it should work.

Changing your local character set (i.e. NLS_LANG) does never affect any existing data in database.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • I followed what you suggest and restart my pc but nothing changed NOTE i am migrating data from access 2003 to oracle 12c so I am opening the access data from sql developers is there anything else to do ? characters in access display right but when I open it from sql developer I get question marks instead of arabic characters – sam Sep 15 '17 at 07:30
  • SQL Developer is Java based which does not use `NLS_LANG` setting, Have a look at SQL Developer program settings (I don't know the menu by head as I don't use this tool so much) – Wernfried Domscheit Sep 25 '17 at 06:17
0

You can try updating using the function UNISTR and the unicode 0644, to circumvent Oracle client configurations (in case they do not support Unicode):

UPDATE LAM_INVESTMENT
SET INVESTMENT=UNISTR('\0644')
WHERE KEY=11;

It may or may not work, depending on the data type of column investment and on your character set (or national character set).

Unicode table reference

Francisco Sitja
  • 963
  • 4
  • 7
0

If you column type is nvarchar, have you tried to use the next query:

UPDATE LAM_INVESTMENT
SET INVESTMENT=N'ل'
WHERE KEY=11;

I had the same situation with korean language. Hope this approach helps you as well as it helped me.

Khazratbek
  • 1,656
  • 2
  • 10
  • 17