[Oracle11g] [Solved] Arabic text update and insert issue in Oracle Database

Issue:  Unable to update or insert Arabic data to a database. Specifically any Arabic data inserted is shown in database as '?????'

Cause:  The issue occurs because the database character set doesn't support  UTF characters.

Resolution: Changing the character set of database to one that supports UTF characters would resolve the issue. 

Check the default character set of the database by running this query,

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

Now if you try inserting or updating any rows with Arabic language data then the data would be displayed as a set of special characters as below,

Log into database as SYS and shutdown the database services giving the below command,

SQL > shutdown immediate;

Now restart the database in restrict mode. When a database is started up in restricted mode then only users with special privileges will be able to access it. We are starting up in restricted mode as we are going to alter a database default value.

SQL > startup restrict

Once the database is up and mounted in restricted mode proceed with changing the default dataset,

SQL > ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Now again issue a shutdown immediate command followed by the startup command,

SQL > startup

Once database has been mounted confirm that the character set has been changed 

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

Now check again in the database to see whether Arabic data can be viewed now.

This is how you change the default characterset of a database.