Storing Arabic Characters in Western Character-set (WE8ISO8859P1) Database

I’ve seen an entire production system running with this setting:
DB Character-set: WE8ISO8859P1
Client NLS_LANG: WE8ISO8859P1

They were storing Arabic characters just fine.

It confused me as how this configuration could store Arabic correctly, and as such, I had to involve Oracle support. The answer from Oracle support was it could work but it is not supported.

Please don’t try it even though it’s good to know this can work.

The main issue you will face with this configuration is exchanging data with other systems whether using db link or exp/imp/data pump. Oracle simply will not do character-set conversion like it would do between other supported Arabic character-set (AR8ISO8859P6, AR8MSWIN1256, etc…).

Here are the steps to move data in such situation:

1. From source database WE8ISO8859P1 spool data to text file comma delimited.

2. From target database with character-set AR8MSWIN1256 create external table with character-set AR8MSWIN1256 (example below)

3. Perform an insert statement at the target.

Example of an external table with AR8MSWIN1256 character-set

SQL> CREATE TABLE read_in_arabic2
2 ( col1 VARCHAR2(255),
3 col2 VARCHAR2(255)
4 )
5 ORGANIZATION EXTERNAL
6 ( TYPE ORACLE_LOADER
7 DEFAULT DIRECTORY “DUMP_DIR”
8 ACCESS PARAMETERS
9 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET AR8MSWIN1256
10 BADFILE dump_dir:’atext.bad’
11 LOGFILE dump_dir:’atext.log’
12 READSIZE 1048576
13 FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM
14 MISSING FIELD VALUES ARE NULL
15 REJECT ROWS WITH ALL NULL FIELDS
16 (
17 col1 CHAR(255) TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘,
18 col2 CHAR(255) TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
19 )
20 )
21 LOCATION
22 ( “DUMP_DIR”:’atext.txt’
23 )
24 )
25 REJECT LIMIT UNLIMITED PARALLEL 4;

Hazem Ameen
Senior Oracle DBA

Advertisements

5 thoughts on “Storing Arabic Characters in Western Character-set (WE8ISO8859P1) Database

  1. I have excel file with arabic data and I need to load it to database with characterset AMERICAN_AMERICA.WE8ISO8859P1. I tried sqlldr, exp/imp but all failed to display arabic characters. client used to display is pl/sql developer which is using supported arabic font and the NLS_LANG in regedit for oracle home is WE8MSWIN1252

    Appreciate your reply

  2. Did not work still the arabic characters showing down up ? . Actually I tried to load the excel file from toad after changing NLS_LANG in registry to be AR8MSWIN1256

    any help
    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s