OraFAQ Forum: SQL & PL/SQL » junk characters passed over db link

Home » SQL & PL/SQL » SQL & PL/SQL » junk characters passed over db link

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

junk characters passed over db link [message #238750]

Sat, 19 May 2007 15:49

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: junk characters passed over db link [message #238752 is a reply to message #238751]

Sat, 19 May 2007 16:05

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

Report message to a moderator

Re: junk characters passed over db link [message #238753 is a reply to message #238752]

Sat, 19 May 2007 16:22

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 26 00:04:45 CDT 2025