PROBLEM NATIONAL CHARACTER

[Arsalan]

Hello Arsalan

The production server database.

1- windows 2003

2-Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
--------------------------------------------------------------------------------------------

DWH DATABASE

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

OS Windows 2008 R2 Enterprise

when i want to display or get data from production  database  into DWH DATABASE

It does not display the correct data in DWH  it show ###$$$###3,

Even if i write query like

select * from address@afgprod;

it does not show correct data of character

i used the blow script but still have problem

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
SHUTDOWN IMMEDIATE;
STARTUP;

not show pashto language character
 

[ErmanArslansOracleBlog]

Hi,

1)What is that character ? (chiness? arabic? ...)

2)What is the tool that you are using for querying the database?

3)What is the client that you are using (windows, mac, linux ...) is it able to show those characters?

4)What is the characterset of your database? Does it include your problematic character?
send me the output of the following sql: (send it in a formatted way)

set linesize 1000
col "SESSION" format a30
col DATABASE  format a30
col INSTANCE  format a30
select * from
(select 'SESSION' SCOPE,nsp.* from nls_session_parameters nsp
union
select 'DATABASE' SCOPE,ndp.* from nls_database_parameters ndp
union
select 'INSTANCE' SCOPE,nip.* from nls_instance_parameters nip
) a
pivot  (LISTAGG(VALUE) WITHIN GROUP (ORDER BY SCOPE)
FOR SCOPE
in ('SESSION' as "SESSION",'DATABASE' as DATABASE,'INSTANCE' as INSTANCE));

[ErmanArslansOracleBlog]

5)What is the column type of the table that stores this problematic character?
(varchar2, nvarchar ... so on)

[Arsalan]

1)What is that character ? (chiness? arabic? ...)

Arabic, we store afghani language like pashto

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

VALUE$
---------------------------------------------------------------------
AL32UTF8

2)What is the tool that you are using for querying the database?

PL/SQL DEVELOPER

3)What is the client that you are using (windows, mac, linux ...) is it able to show those characters?

Windows 2008 and windows 7

4)What is the characterset of your database? Does it include your problematic character?
send me the output of the following sql: (send it in a formatted way)

PARAMETER                                                                        SESSION                        DATABASE                       INSTANCE
-------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
NLS_COMP                                                                         BINARY                         BINARY                         BINARY
NLS_SORT                                                                          BINARY                         BINARY                        
NLS_CALENDAR                                                                  GREGORIAN                   GREGORIAN                  GREGORIAN
NLS_CURRENCY                                                                  $                              $                              
NLS_LANGUAGE                                                                  AMERICAN                     AMERICAN                   AMERICAN
NLS_TERRITORY                                                                  AMERICA                       AMERICA                        AMERICA
NLS_DATE_FORMAT                                                            DD-MON-RR                  DD-MON-RR                      
NLS_TIME_FORMAT                                                             HH.MI.SSXFF AM              HH.MI.SSXFF AM                
NLS_CHARACTERSET                                                           AL32UTF8                      
NLS_ISO_CURRENCY                                                            AMERICA                        AMERICA                        
NLS_DATE_LANGUAGE                                                         AMERICAN                      AMERICAN                      
NLS_DUAL_CURRENCY                                                         $                              $                              
NLS_RDBMS_VERSION                                                         11.2.0.3.0                    
NLS_TIME_TZ_FORMAT                                                       HH.MI.SSXFF AM TZR        HH.MI.SSXFF AM TZR            
NLS_NCHAR_CONV_EXCP                                                    FALSE                              FALSE                          FALSE
NLS_LENGTH_SEMANTICS                                                    BYTE                               BYTE                           BYTE
NLS_TIMESTAMP_FORMAT                                                   DD-MON-RR HH.MI.SSXFF AM       DD-MON-RR HH.MI.SSXFF AM      
NLS_NCHAR_CHARACTERSET                                               AL16UTF16                      
NLS_NUMERIC_CHARACTERS                                                  .,                             .,                            
NLS_TIMESTAMP_TZ_FORMAT                                              DD-MON-RR HH.MI.SSXFF AM TZR   DD-MON-RR HH.MI.SSXFF AM TZR  
20 rows selected
--------------------------------------------------------
5)What is the column type of the table that stores this problematic character?
(varchar2, nvarchar ... so on)

MAILING_ADDRESS             VARCHAR2(50 CHAR)  Y
AREA_VILLAGE                   VARCHAR2(100 CHAR) Y        

[ErmanArslansOracleBlog]

In your previous comment, I saw that you are using a dblink. (@afgprod)

so, you are querying this data, from another database and you are using db link to do that?

That means, you have problem displaying data when querying it with a "dblink"?
can you confirm this?

[Arsalan]

Hi,

Yes displaying data problem .


Actually  i want to bring data from production database the version is below

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

so in this database everything is ok it does not have any problem

when ever i want to bring data from production database or query to display data from prod db

in DWH DATABASE  in below version

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

OS Windows 2008 R2 Enterprise  

for example

select * from tax_payer@afgprod;

it does not display the character properly like $$$$$$$$$$$$$$$$$.

i do know it display problem or database

thanks for your kind support.

[ErmanArslansOracleBlog]

Displaying data problem, for sure.

But please confirm -> "it is only happening when using a dblink"?

[Arsalan]

Sure, we are using dblink between production database and DWH Database.

[ErmanArslansOracleBlog]

What is the character set of the database that you are using the database link? ( I mean the db link's source db)

Note: if its characterset is different than the characterset of your db named AFGPROD, the data will be converted into the its characterset as you retrieve it.

[Arsalan]

1-The below character set of production database is

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

VALUE$
---------------------------------

AL32UTF8

2-The below character set of DWH Database.


SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
VALUE$
---------------------------
WE8MSWIN1252

We create dblink from dwh database to production database.

mean we query data from production database in DWH Database.


Character set is different

[ErmanArslansOracleBlog]

Okay.

The problem is obvious . The character set of DWH is WIN1252.

WIN1252 characterset is not suitable for arabic characters, it is for Western European.

You can consider changing that character set.

Changing the Database Character Set ( NLS_CHARACTERSET ) [ID 225912.1]

[Arsalan]

Thanks Arsalan,

I am very thankful that you always supported and guide me when ever i have some query or problem.

so i followed the below script which  solved database character problem.


 SHUTDOWN IMMEDIATE;

 STARTUP MOUNT;

 ALTER SYSTEM ENABLE RESTRICTED SESSION;

 ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

 ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET INTERNAL_USE  AL32UTF8;

SHUTDOWN IMMEDIATE;

STARTUP;

[ErmanArslansOracleBlog]

Good for you Arsala.

Thanks for the feedback.