Arabic Characters Displayed in Question Marks

 Arabic Characters Displayed in  ?? ???

      In the name of Allah, Most Gracious, Most Merciful

Praise be to Allah, blessing and peace be upon our prophet Mohammed, his family and his companions. After that

              A frequently asking question about how to avoid retrieving Arabic characters in question marks. In Shaa Allah, today i will explain the reasons for such a problem and how to overcome it. Since it is a common and annoying problem facing most developers who are not familiar with Oracle character-set pre-installation requirements.

  

Key Problem:

    Data retrieved from database into forms in question marks ????? ??? ?????   

Key Reason:

 In fact, this problem starts on setting up "Oracle database". The Oracle Universal Installer always makes checks on the default language you specified  for your Windows Operating System.

This check makes sense to The Oracle database Installer to select the logical Character-set that fits your requirements.

     Determining Oracle Database encoding method: 

 SELECT parameter , value

     FROM NLS_DATABASE_PARAMETERS

    ;’% WHERE parameter like ‘%CHARACTERSET

PARAMETER                                        VALUE

—————————— —————————————-

NLS_CHARACTERSET                       WE8MSWIN1252

NLS_NCHAR_CHARACTERSET         AL16UTF16

 

  The Problem Reasons Conclusion   

There are two precise reasons for such problems(Question marks & junck):

1.      Incompatible"NLS_LANG" parameter with your default Windows Operating System's language.

2.     In-compatible character-set between Oracle database Server and the Client-side (forms and reports) applications.

  In other words, just for instance, if Oracle database uses UTF-8 characters, thus your client's Oracle  forms and reports applications  MUST  use the same UTF-8 Character set too. Otherwise, you will face language issues for incompatible character-set.

Key Solution:

•  Re-installing the Oracle database with the correct character-set also expensive in time, effort and not a good alternative solution.

       Warning

Enabling the default Arabic Language on your server or pc is a necessary BEFORE reinstalling Oracle Database    ?WHY it guarantees a PERFECT selection of the Arabic and English character-set for inserting Arabic Letters  correctlybut, you will still having your OLD DATA that had inserted with the wrong character-set retrieved in question marks ??? ??? ?? so you have to re-entered it with the correct character-set.

• Creating a new Oracle Database Instance.

    Warning:

              Oracle Express Edition is limited to a single instance on any server     Oracle licensing terms don't allow for more than one instance on a given server 

             If you need more database instances, you need to download

             Either Oracle Database Standard or Enterprise Edition.

      This solution implies two necessary steps, in some cases, related to each other.  

1.     Client Side:

                   Create a New User environment Variable named 'NLS_LANG'  give the                         following value: AMERICAN_AMERICA.AR8MSWIN1256

          2.     Oracle Database Server Side: 

                  The following workaround will help you to avoid removing and re-installing                    a new Oracle database.

   

   Create a New Oracle Database Instance Steps

1)  Take a back up from the current database.

2) Select Start > All Programs > Oracle - OraDb10g_home1 > Configuration and migration Tools >Database Configuration Assistant >Create a Data Base

3)(Press Next)    > General Purpose  (Press Next) > Global Database Name (SID)   (Press Next)  > Configure the data base with Enterprise Manager (no changes)

4) (Press Next)   > Specify  a password for your account  & confirm it (Press Next)  > File System (No Changes) (Press Next)  > Use Database file Location from Template (No Changes)

5) (Press Next)   > Specify Flash Recovery Area (No Changes) (Press Next) > Sample Schema (No Changes)

6) (Press Next)   > (a Window with 4 tabs appeared ) Select the Character Sets 's tab use the Default (Only and only if your Default Language was set to Arabic)

(Notice: AR8MSWIN1256) (IF not choose from the list of the character sets that AR8MSWIN1256) (u have also the default  Language and date u don't have to change) 

7) Data base Storage (No Changes) (Press Next)    >

8) Create Database (No Changes)  (Press Next)  > Then  Press  Finish  >

9) A General Purpose Summary Window will appear (Notice the Character Sets is now that AR8MSWIN1256 we r looking for it as selected)

10) Press OK Now the Creation Process of the data base will Start as if u r installing a new database from the beginning up to the end then Press Exit button

11) Connect your db with the new password u previously included in the Wizard and

 import the old dump then try to connect your forms with the new db,enter the data,

 save & Retrieved.

Note: The following are successful examples.

Arabic characters are displaying as question marks in forms 10g.
Oracle Forms 11g with Arabic and English prompts

How to Test New effects  

   Fixing the problem DOES NOT CORRECT the wrong data the user had input. 
You have to RE-ENTER new data, store and retrieve it.
  Changing"NLS_LANG" parameter has NO affect on the Old stored data. 

 Run the following query. The output of your correct query result should be as follow:

SQL>  SELECT * FROM v$nls_parameters

  2  WHERE parameter in ('NLS_CHARACTERSET','NLS_LANGUAGE');





PARAMETER                           VALUE

-------------------------------   ---------------------------------

NLS_LANGUAGE                 ARABIC



NLS_CHARACTERSET       AR8MSWIN1256

Note: If  no change in data base "NLS_LANG" occurs then you have to restart the database to refresh its "NLS_LANG" with the new parameter  value.

Hope it Helps…

"Read about : Three Steps for Customizing Oracle Form Informative Messages"

My success only comes from Allah, pls. note your comments and suggestions are great help for me in progress thanks in advance.