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:
You have successfully completed the task!
Cheers!
