Oracle and Arabic   April 7th, 2007

Even though I can claim that I understand ORACLE National Language support NLS especially when it comes to Arabic.
I was involved in literally hundreds of issues where Arabic was not showing properly,
I cannot remember one situation where things did not work.
Two weeks back I was confronted with a situation that does not seem to be related to NLS, but it was. It is very tricky and difficult to diagnose

We were reading images from Oracle Database and displaying them on the WEB using the traditional PL/SQL WEB Toolkit.
Something that we have done several times previously

I used code from previous projects, also downloaded from the internet, but the images simply wont show.

The following is a example of a PL/SQL procedure that should work, but it did'nt

What was the problem

Look at the function highlighted above  cast_to_varchar2(l_raw)

It converts raw data to varchar2.  It turned out that it also does character set conversation while its performing its duties.

It, therefore, seems to converting characters that are not intended to be dealt with as characters, but only binary information. However, the image raw information seemed to have been converted to other representation due to the character set conversion.

This should be easily solved

Just change the character set setting in you client (Profile, registry etc..) to that same character set of the database and character set conversion should stop.

The database was created with UNICODE AL16UTF16.

So I modified the Database Access Descriptor (DAD) setting of the Oracle Application Server to AL16UTF16. Still however, I could not make the image to appear on the web page.  The only way it eventually worked, was to connect against a database with different character set. We have at our env. a database created with AR8MSWIN1256. The darn images finally were displayed.

I Still do not know why AL16UTF16 does not work (probably has to do with other regional setting on our Linux platform). Therefore, I decided to change the character set of the Oracle database to AR8MSWIN1256 which brings me to the other subject

Changing the Oracle database Character Set

Note: This is procedure is NOT supported by Oracle, therefore, execute at your risk  and take a backup before you start