Home » RDBMS Server » Server Utilities » Loading of foreign text (11g)
Loading of foreign text [message #454527] Thu, 06 May 2010 04:00 Go to next message
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?
Re: Loading of foreign text [message #454533 is a reply to message #454527] Thu, 06 May 2010 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
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
Re: Loading of foreign text [message #454571 is a reply to message #454527] Thu, 06 May 2010 05:34 Go to previous messageGo to next message
Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom
Member
thanks guys.
Re: Loading of foreign text [message #454591 is a reply to message #454527] Thu, 06 May 2010 06:48 Go to previous messageGo to next message
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 'D’Costa'.

Hows that?
Re: Loading of foreign text [message #454593 is a reply to message #454591] Thu, 06 May 2010 06:55 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You are still using the wrong NLS parameters.
Re: Loading of foreign text [message #454609 is a reply to message #454527] Thu, 06 May 2010 08:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
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
icon14.gif  Re: Loading of foreign text [message #454614 is a reply to message #454527] Thu, 06 May 2010 08:29 Go to previous messageGo to next message
Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom
Member
Thanks
Re: Loading of foreign text [message #454749 is a reply to message #454527] Fri, 07 May 2010 03:49 Go to previous messageGo to next message
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?
Re: Loading of foreign text [message #454756 is a reply to message #454749] Fri, 07 May 2010 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT?

Regards
Michel
Re: Loading of foreign text [message #454760 is a reply to message #454527] Fri, 07 May 2010 05:06 Go to previous messageGo to next message
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.
Re: Loading of foreign text [message #454761 is a reply to message #454760] Fri, 07 May 2010 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
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
Re: Loading of foreign text [message #454762 is a reply to message #454527] Fri, 07 May 2010 05:23 Go to previous messageGo to next message
Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom
Member
ok, do you know the SELECT then?
Re: Loading of foreign text [message #454765 is a reply to message #454762] Fri, 07 May 2010 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
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
Re: Loading of foreign text [message #454768 is a reply to message #454527] Fri, 07 May 2010 05:57 Go to previous messageGo to next message
Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom
Member
ok, thanks for your help Smile
Re: Loading of foreign text [message #454777 is a reply to message #454768] Fri, 07 May 2010 06:40 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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.
Re: Loading of foreign text [message #454786 is a reply to message #454527] Fri, 07 May 2010 07:35 Go to previous messageGo to next message
Ravin_C
Messages: 38
Registered: February 2008
Location: United Kingdom
Member
ok, thanks.
Re: Loading of foreign text [message #454788 is a reply to message #454777] Fri, 07 May 2010 07:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67956
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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)?
Re: Loading of foreign text [message #454902 is a reply to message #454527] Sat, 08 May 2010 06:34 Go to previous messageGo to next message
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.
Re: Loading of foreign text [message #454921 is a reply to message #454902] Sat, 08 May 2010 09:18 Go to previous message
Michel Cadot
Messages: 67956
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
Previous Topic: IMP with different language settings
Next Topic: importing table data is not comming
Goto Forum:
  


Current Time: Tue Sep 28 19:54:59 CDT 2021