Oracle Database - Arabic Support

This document will encompass how an Oracle Database server can be made to support Arabic Data.

1.       In regedit go to local machine - > Software -> ORACLE -> KEY_OraDb11g_home1

a.       Check whether the NLS_LANG = ‘AMERICAN_AMERICA.WE8MSWIN1252’

b.      If not, modify it and make is ‘AMERICAN_AMERICA.WE8MSWIN1252’

2.       In Environmental Variables set the following environmental variables:

a.       NLS_LANG : AMERICAN_AMERICA.AR8MSWIN1256

b.      NLS_CHARACTERSET: AR8MSWIN1256

3.       Once this is performed, log into your Db using SQL Developer as DBA.

a.       Perform the following Query

select distinct(nls_charset_name(charsetid)) CHARACTERSET,

decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKNOWN'),

9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'),

96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'),

8, decode(charsetform, 1, 'LONG', 'UNKNOWN'),

112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN

from sys.col$ where charsetform in (1,2) and type# in (1, 8, 9, 96, 112)

order by CHARACTERSET, TYPES_USED_IN;

                                This query should only retrieve 7 rows having only 2 charactersets.(If it has more than 7 rows, this data is incorrect as somewhere down the line, the db has got corrupted)

4.       Run the following query and check if it returns any rows. These rows are the corrupted ones!

select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL#

from sys.col$ where CHARSETID=NLS_CHARSET_ID('AL32UTF8') and charsetform ='1')

order by OWNER, TABLE_NAME, COLUMN_NAME;

5.       Run the following query and perform a check for the following:

SELECT * FROM NLS_DATABASE_PARAMETERS;

NLS_LANGUAGE	AMERICAN
NLS_TERRITORY	AMERICA
NLS_CHARACTERSET	AR8MSWIN1256

 

6.       Perform the below mentioned queries:

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

Output: AR8MSWIN1256

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

Output: AL16UTF16

7.       If the results of step 5 and 6 are different than what is mentioned the next step brings it inline.

8.       This step is very important. Having logged in as Dba in SQL Developer, run the following Query.

UPDATE PROPS$

SET VALUE$ = 'AR8MSWIN1256'

WHERE NAME = 'NLS_CHARACTERSET';

This will update one record.

Repeat Step 5 and 6.

9.       Stop the OracleOraDb11g_home1TNSListener Service.

10.   This is the step that resets all values as per our requirement. Most Important step.        

a.       Log into SQL Plus as dba.

b.      Run the following Script.

Spool charfix.log
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SHUTDOWN IMMEDIATE;
STARTUP;
-- yes, 2 times startup/shutdown . This is not a typo
SHUTDOWN IMMEDIATE;
STARTUP;
spool off

c.       This will take 2-4 minutes to execute.

11.   Once this is executed, start the OracleOraDb11g_home1TNSListener Service.

12.   Login into SQL Developer and check Step 5 and 6.

13.   Now, you will be able to insert Arabic data in your database.

Note that the characterset mentioned here is the one for Arabic.

The different charctersets available are mentioned in this link:

http://download.oracle.com/docs/html/B10131_02/gblsupp.htm

You have successfully completed the task!

Cheers!