OraFAQ Forum: Server Utilities » Loading of foreign text

Home » RDBMS Server » Server Utilities » Loading of foreign text (11g)

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

Loading of foreign text [message #454527]

Thu, 06 May 2010 04:00

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

Hi guys,

When i load a name such as 'DÂ'Costa' using sql loader, after loading in Oracle it becomes 'DÂ¿Costa'.

Any ideas guys?

Report message to a moderator

Re: Loading of foreign text [message #454533 is a reply to message #454527]

Thu, 06 May 2010 04:04

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

By using the appropriate NLS parameters.
Which mean post the current values.

Regards
Michel

Report message to a moderator

Re: Loading of foreign text [message #454571 is a reply to message #454527]

Thu, 06 May 2010 05:34

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

thanks guys.

Report message to a moderator

Re: Loading of foreign text [message #454591 is a reply to message #454527]

Thu, 06 May 2010 06:48

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

But during transfer from table to table it get changed from 'DÂ¿Costa' to 'DCosta'.

Hows that?

Report message to a moderator

Re: Loading of foreign text [message #454593 is a reply to message #454591]

Thu, 06 May 2010 06:55

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

You are still using the wrong NLS parameters.

Report message to a moderator

Re: Loading of foreign text [message #454609 is a reply to message #454527]

Thu, 06 May 2010 08:13

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

the problem is can't predict the input (NLS) language, its loaded as part of a batch process and the data is multi national.

[Updated on: Thu, 06 May 2010 08:14]

Report message to a moderator

Re: Loading of foreign text [message #454611 is a reply to message #454609]

Thu, 06 May 2010 08:25

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And then you can set the NLS parameters.
At least you should know in which character set the input file comes.
If you can't, then nothing can be done.
NLS parameters is the way you tell Oracle how to see/interpret the data.

Regards
Michel

Report message to a moderator

Re: Loading of foreign text [message #454614 is a reply to message #454527]

Thu, 06 May 2010 08:29

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

Thanks

Report message to a moderator

Re: Loading of foreign text [message #454749 is a reply to message #454527]

Fri, 07 May 2010 03:49

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

How could i pull(sql)and see all the foreign & gibbrish characters that exists in the datadase.

Please help?

Report message to a moderator

Re: Loading of foreign text [message #454756 is a reply to message #454749]

Fri, 07 May 2010 04:56

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SELECT?

Regards
Michel

Report message to a moderator

Re: Loading of foreign text [message #454760 is a reply to message #454527]

Fri, 07 May 2010 05:06

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

yes please, i have to identify all the affected ones so that the DBA can perform a clean up.

Report message to a moderator

Re: Loading of foreign text [message #454761 is a reply to message #454760]

Fri, 07 May 2010 05:07

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Not the work for the DBA, A DBA manages the database not the data, it is your data, you have to provide him/her the script to fix your data.

Regards
Michel

Report message to a moderator

Re: Loading of foreign text [message #454762 is a reply to message #454527]

Fri, 07 May 2010 05:23

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

ok, do you know the SELECT then?

Report message to a moderator

Re: Loading of foreign text [message #454765 is a reply to message #454762]

Fri, 07 May 2010 05:50

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't know and no one can know here but you.
It YOUR tables and YOUR data.

Regards
Michel

Report message to a moderator

Re: Loading of foreign text [message #454768 is a reply to message #454527]

Fri, 07 May 2010 05:57

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

ok, thanks for your help Smile

Report message to a moderator

Re: Loading of foreign text [message #454777 is a reply to message #454768]

Fri, 07 May 2010 06:40

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You'll probably want to use the ascii function to find the foreign characters, but it's going to be slow.

Report message to a moderator

Re: Loading of foreign text [message #454786 is a reply to message #454527]

Fri, 07 May 2010 07:35

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

ok, thanks.

Report message to a moderator

Re: Loading of foreign text [message #454788 is a reply to message #454777]

Fri, 07 May 2010 07:41

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And even then, the ASCII code could be correct for a character set and not for the other, the character should been converted and then is a valid one but not correct one for the data, and so on.

For instance, if I import "é" in a US7ASCII database it is converted to "e" but not all "e" comes from an "é".

Regards
Michel

[Updated on: Fri, 07 May 2010 08:06]

Report message to a moderator

Re: Loading of foreign text [message #454791 is a reply to message #454788]

Fri, 07 May 2010 07:56

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

In that case I assume you can't tell which data come from a foreign character once it's in the DB (assuming it got converted obviously)?

Report message to a moderator

Re: Loading of foreign text [message #454902 is a reply to message #454527]

Sat, 08 May 2010 06:34

Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom

Member

yep, absolutely correct, thats why i am trying to identify them to clean up.

Report message to a moderator

Re: Loading of foreign text [message #454921 is a reply to message #454902]

Sat, 08 May 2010 09:18

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, there is no automatic methid just a manual one, row per row, and only those who know the data can do it.

Regards
Michel

Report message to a moderator

Previous Topic:	IMP with different language settings
Next Topic:	importing table data is not comming

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 20:57:50 CDT 2024