Friday, July 29, 2011

Sun IDM + character unicode issue on Oracle database as a resource

Issue:I hope many people fcaed the issue with chinese character.When we update the chinese character(like 璐英') in Oracle 10 g database as user provisioning on resource,Data are not updated proper format showing chinese character value as a ¿¿ upside-down question marks.If any other applications uses this database records,It is not reflected proper format(chinese character)

I have faced similar type of issue below I am posting solution for this issue.

Solution:
I have created two database in my local machine
case 1) database name localtest has character set value as below

PARAMETER VALUE
NLS_CHARACTERSET WE8MSWIN1252 single byte as in my current database
NLS_NCHAR_CHARACTERSET AL16UTF16

1)created the table
CREATE TABLE TEST2(NAME VARCHAR2(50)); attribute type VARCHAR2

2)Insert One value with chiness character
INSERT INTO TEST2 VALUES ('Cé璐英');

3)check result for this
SELECT * FROM TEST2;

geeting value -- Cé¿¿ upside-down question marks


Case 2). database name localserver has character set value as below

PARAMETER VALUE
NLS_CHARACTERSET AL32UTF8 --- Multibyte character set
NLS_NCHAR_CHARACTERSET AL16UTF16


1)created the table
CREATE TABLE TEST2(NAME NVARCHAR2(50));

2)Insert One value with chiness character
INSERT INTO TEST2 VALUES ('Cé璐英');

3)check result for this
SELECT * FROM TEST2;

geeting value -- Cé璐英 Correct chinese character as insert.
WE8ISO8859P15
 ISO 8859-15 West European

While AL32UTF8
 Unicode 4.0 UTF-8 Universal character set

Database character set (NLS_CHARACTERSET) :- The character encoding scheme used by the database is defined as part of the CREATE DATABASE statement. All SQL CHAR datatype columns (CHAR, CLOB, VARCHAR2, and LONG), including columns in the data dictionary, have their data stored in the database character set. In addition, the choice of database character set determines which characters can name objects in the database.
National character set (NLS_NCHAR_CHARACTERSET):- A national character set is an alternate character set that enables you to store Unicode character data in a database that does not have a Unicode database character set.
NLS_CHARACTERSET              :- Database character set         
NLS_NCHAR_CHARACTERSET :- National character set

1) Below combination uses western European database characterset & AL16UTF16 national character set to store UNICODE data.
It means we can’t store Unicode database directly in CHAR,VARCHR2 data types , We have to Use NCHAR, NVARCHAR2 data types.
NLS_CHARACTERSET               WE8ISO8859P15
NLS_NCHAR_CHARACTERSET         AL16UTF16


2) This is UNICODE database character set use to store multilingual charters.
NLS_CHARACTERSET                      AL32UTF8
NLS_NCHAR_CHARACTERSET         AL16UTF16

AL32UTF8 will support the chinese character as well other languages.

 it is not simple method,  need to do Characterset conversion Migration activity
The easiest way to do the conversion is to export the database, drop the database, recreate it with the new characterset and perform an import.

No comments:

Post a Comment