Home » SQL & PL/SQL » SQL & PL/SQL » junk characters passed over db link
junk characters passed over db link [message #238750] Sat, 19 May 2007 15:49 Go to next message
get2audi
Messages: 2
Registered: May 2007
Junior Member
Hi,
I'm facing a problem while fetching string data from a table in remote database over db link :

Source db :

SQL> select field_desc from mdm_hierarchy_data where field_code = '01000000103236';

FIELD_DESC
------------------------------------------------------------------------------------------------------------------------
01000000103236:MacData i Gavle AB
01000000103236:MacData i Gavle AB

Destination db :
Some characters get converted to non-English. I need to write this data into CSV file. I get junk characters in the file, due to this issue.


SQL> select FIELD_DESC from mdm_hierarchy_data@TEST_MDM_LINK a where a.field_code = '01000000103236';

FIELD_DESC
------------------------------------------------------------------------------------------------------------------------
01000000103236:MacData i Gävle AB
01000000103236:MacData i Gävle AB

For example , here, the 'a' from source db is showing as 'ä' in dest.
Please help !!!

[Updated on: Sat, 19 May 2007 15:50]

Report message to a moderator

Re: junk characters passed over db link [message #238751 is a reply to message #238750] Sat, 19 May 2007 15:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure that ä is not in the source db but your source does not see it?

To know the reason post:
- select dump(field_desc) from mdm_hierarchy_data where field_code = '01000000103236';
- select value from nls_database_parameters where parameter='NLS_CHARACTERSET'; (on both databases)
- select value from v$nls_parameters where parameter='NLS_CHARACTERSET'; (on both clients, source and remote)
- Oracle versions (4 decimals, for both databases and clients)

Regards
Michel
Re: junk characters passed over db link [message #238752 is a reply to message #238751] Sat, 19 May 2007 16:05 Go to previous messageGo to next message
get2audi
Messages: 2
Registered: May 2007
Junior Member
Hi Michel,
Thanks for quick reply.

Here's the info you asked for :

1) select dump(field_desc) from mdm_hierarchy_data where field_code = '01000000103236';

Typ=1 Len=34: 48,49,48,48,48,48,48,48,49,48,51,50,51,54,58,77,97,99,68,97,116,97,32,105,32,71,195,164,118,108,101,32,65,66
Typ=1 Len=34: 48,49,48,48,48,48,48,48,49,48,51,50,51,54,58,77,97,99,68,97,116,97,32,105,32,71,195,164,118,108,101,32,65,66

2) NLS_CHARACTERSET is 'UTF8' for both databases in both nls_database_parameters and v$nls_parameters

3) Oracle versions :

Source db : 10.2.0.2.0
Dest db : 9.2.0.7.0

Re: junk characters passed over db link [message #238753 is a reply to message #238752] Sat, 19 May 2007 16:22 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note the "195,164" this is your "ä".
So this character is in your database and your source client does not display it correctly.
It uses a replacement character ('a') instead so I think its session is not with UTF8 character set.

Regards
Michel
Previous Topic: SQL Server to Oracle data migration
Next Topic: Getting Errors in PL/SQL Collections code Execution
Goto Forum:
  


Current Time: Thu Dec 08 00:26:36 CST 2016

Total time taken to generate the page: 0.08431 seconds