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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s