Home » RDBMS Server » Server Administration » Unable to create spanish characters (ORACLE 10G on RHEL 5 )  () 1 Vote
Unable to create spanish characters [message #411862] Tue, 07 July 2009 03:34 Go to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi All,

I wanted a database which supports spanish characters.
I created a database using "AL32UTF8" which supports multi lang.
But then the result was not appearing.

Following is the steps which explains in detail.

********************************************************************************************************
SQL> create table spanishtab(name nvarchar2(100));

Table created.

SQL> insert into spanishtab values('&NAME');
Enter value for name: ¿Qué Hay De Nuevo?
old 1: insert into spanishtab values('&NAME')
new 1: insert into spanishtab values('¿Qué Hay De Nuevo?')

1 row created.


SQL> select * from spanishtab;

NAME
--------------------------------------------------------------------------------
??Qu?? Hay De Nuevo?


SQL> select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ------------------------------------------------------------------------------------------------------------------------------------- ---------------------------
NLS_CSMIG_SCHEMA_VERSION 5
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0

21 rows selected.


*********************************************************************************************************


Please Update the same.

Thanks,
Sanjay
Re: Unable to create spanish characters [message #411874 is a reply to message #411862] Tue, 07 July 2009 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your SESSION parameter NLS_LANG is not correctly set.
Maybe you SQL*Plus does not support these characters (to verify this just spool the result in a file and open it with your favorite editor).

Regards
Michel
Re: Unable to create spanish characters [message #411902 is a reply to message #411874] Tue, 07 July 2009 05:04 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi Michel,

I tried as u said i.e,
export NLS_LANG=American_America.AL32UTF8

It actually worked for me.Thanks a lot. Now i m able to create spanish characters.
Now the problem here is the spanish characters which are already present in the user schema is not getting updated even after i recreate the user and dump the logical schema.


How can we over come this,,,!!

Thanks,
Sanjay.S
Re: Unable to create spanish characters [message #411907 is a reply to message #411902] Tue, 07 July 2009 05:11 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Thanks for the feedback.

>>Now the problem here is the spanish characters which are already present in the user schema is not getting updated even after i recreate the user and dump the logical schema.

I hope spanish characters are avaliable in user schema; how your checking?? Are you using any tool?

Babu
Re: Unable to create spanish characters [message #411909 is a reply to message #411907] Tue, 07 July 2009 05:15 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
I actually know which tables contains those spanish characters and when queried i got some thing like this below
{
Riesgo de Mercado � Nivel B�sico (Bibliotecas de 8 Cursos)
Gesti�n de Riesgo Operacional (Bibliotecas de 21 Cursos)
}

Re: Unable to create spanish characters [message #411914 is a reply to message #411907] Tue, 07 July 2009 05:18 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Okay. Could please tel us how your executing SQL Query? Are you using Toad? SQLPLUS? iSQL*Plus? or Any other tool?

Also; Try to avoid duplicate post.

Babu
Re: Unable to create spanish characters [message #411921 is a reply to message #411914] Tue, 07 July 2009 05:24 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
we i used SQLPLUS to run a query.

and sorry for duplicate post. my page waz not displaying and i just refreshed and was unaware of the duplicate pots created.

and thanks for the reply,please update the same.

Thanks,
Sanjay
Re: Unable to create spanish characters [message #411926 is a reply to message #411921] Tue, 07 July 2009 05:33 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
Maybe you SQL*Plus does not support these characters (to verify this just spool the result in a file and open it with your favorite editor).


Babu
Re: Unable to create spanish characters [message #411944 is a reply to message #411926] Tue, 07 July 2009 05:57 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
But i m able to create a new records which can hold spanish characters using SQLPLUS.!!!
Re: Unable to create spanish characters [message #411952 is a reply to message #411944] Tue, 07 July 2009 06:02 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


We are discussing about how to view records not insert statements.

Can you try to view your data from TOAD

Thanks
Re: Unable to create spanish characters [message #412014 is a reply to message #411952] Tue, 07 July 2009 07:37 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi

I tried to view data through TOAD, the characters remains same.
Its not displaying spanish letters correctly.

Wel what needs to be done for supporting in SQLPLUS,,!!!



Re: Unable to create spanish characters [message #412023 is a reply to message #412014] Tue, 07 July 2009 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Wel what needs to be done for supporting in SQLPLUS,,!!!

Nothing if what I mentioned does not work.
Did you try it?

Regards
Michel
Re: Unable to create spanish characters [message #412032 is a reply to message #412023] Tue, 07 July 2009 08:20 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Yes, i have installed TOAD ver 8.5.0.50g on windows and tried to access the server m/c.

Spanish Display in TOAD: An¿lisis de Cr¿dito (Bibliotecas de 13 Cursos)

Correct Spanish Sentence: Análisis de Crédito (Bibliotecas de 13 Cursos)


Just not getting whats missing....!!

Re: Unable to create spanish characters [message #412034 is a reply to message #412032] Tue, 07 July 2009 08:28 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

http://www.quest.com/Quest_Site_Assets/PDF/ReleaseNotes861.htm

Quote:
IMPORTANT: To avoid data corruption, the NLS_LANG setting in the Oracle home used by Toad must be identical to the system active code page associated with the user's system locale.
Re: Unable to create spanish characters [message #412036 is a reply to message #412032] Tue, 07 July 2009 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Yes, i have installed TOAD ver 8.5.0.50g on windows and tried to access the server m/c.

So the answer is NO as what is said is:
Michel Cadot wrote on Tue, 07 July 2009 11:07
Your SESSION parameter NLS_LANG is not correctly set.
Maybe you SQL*Plus does not support these characters (to verify this just spool the result in a file and open it with your favorite editor).

Regards
Michel

Re: Unable to create spanish characters [message #412042 is a reply to message #412036] Tue, 07 July 2009 09:26 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi All,

Thanks for all the above replies
My Real question is not about TOAD.

Thing is i was not able to view the spanish characters correctly which were already dumped in the server machine using SQLPLUS.

Where as when i create a new record with spanish characters,
i m able to view it correctly.

Here i m migrating a user schema having spanish characters dislplaying correctly from oracle 8i having US7ASII as char set

TO

ORACLE 10G using AL32UTF8 char set.


So thing here is migration of data has not properly happened.


Note:
When i use US7ASCII charset in ORACLE 10G, i see a the correct display of all the spanish characters, but not in the case of AL32UTF8 char set.

SO please update on the same scenario.

Thanks in adv,
Sanjay






Re: Unable to create spanish characters [message #412047 is a reply to message #412042] Tue, 07 July 2009 09:37 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
Here i m migrating a user schema having spanish characters dislplaying correctly from oracle 8i having US7ASII as char set

TO

ORACLE 10G using AL32UTF8 char set.


before migrade your database you may need to check your data's using database scaner utility (CSSCAN)

Thanks
Re: Unable to create spanish characters [message #412061 is a reply to message #412042] Tue, 07 July 2009 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Here i m migrating a user schema having spanish characters dislplaying correctly from oracle 8i having US7ASII as char set

US7ASCII does not support spanish character if you are do do this with your 8i database then it means that your database is corrupted.
You cannot recover these data with migration, you have to migrate and then fix them MANUALLY.

Regards
Michel
Re: Unable to create spanish characters [message #412197 is a reply to message #412061] Wed, 08 July 2009 03:45 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi All,

Thanks on the updations.
I See lots of lossy conversions when i try to switch from US7ASCII TO AL32UTF8 using CSSCAN utility.(Major part is of spanish letters)

Here i have taken oracle 8i user schema having US7ASCII charater set AND dumped to ORACLE 10g having US7ASCII character set and tried to change in 10g to AL32UTF8 characyer set.

Thing is i am able to view the spanish characters correctly in 10G US7ASCII character set but not when migrated to AL32UTF8.

How can we overcome these lossy datas and how can we successfully
migrate from ORACLE 8i US7ASCII to ORACLE 10G AS32UTF8,,!
Please asssist..

Thanks,
Sanjay.S

[Updated on: Wed, 08 July 2009 03:51]

Report message to a moderator

Re: Unable to create spanish characters [message #412201 is a reply to message #412197] Wed, 08 July 2009 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You cannot recover these data with migration, you have to migrate and then fix them MANUALLY.

Regards
Michel
Re: Unable to create spanish characters [message #412205 is a reply to message #412201] Wed, 08 July 2009 04:00 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
How can we overcome these lossy datas and how can we successfully
migrate from ORACLE 8i US7ASCII to ORACLE 10G AS32UTF8,,!
Please asssist..


Please check with CSSCAN utility..

Babu

[Updated on: Wed, 08 July 2009 04:00]

Report message to a moderator

Re: Unable to create spanish characters [message #412209 is a reply to message #412205] Wed, 08 July 2009 04:11 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
okay,,wel when i used CSSSCAN utility and from the scan.err file i got lots of lossy msgs one of them is saying as below.

*************************************************************************
User : alpha
Table : PRODUCT
Column: DESCRIPTION
Type : VARCHAR2(1000)
Number of Exceptions : 15
Max Post Conversion Data Size: 989

ROWID Exception Type Size Cell Data(first 30 bytes)
------------------ ------------------ ----- ------------------------------
AAAMjYAAFAAADwNAAE lossy conversion Deals with methodologies for r
AAAMjYAAFAAADwNAAR lossy conversion Covers fundamentals of asset s
AAAMjYAAFAAADwNAAU lossy conversion An introduction to time value

************************************************************************

Wht does the field "Max Post Conversion Data Size: 989 " signifies,,,!! how to resolve this..!!

and as you said to modify it individually after migration, please eloborate on this.

And the other point is does AL32UTF8 supports chinese characters?

Please update the same.

Thanks,
Sanjay.S

Re: Unable to create spanish characters [message #412216 is a reply to message #412209] Wed, 08 July 2009 04:24 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Max Post Conversion Data Size: 989

This means that after migration the size of the greatest value in this field is 989 bytes. There is nothing to fix.

Quote:
and as you said to modify it individually after migration, please eloborate on this.

You migrate then you use UPDATE on all values that are not correct.

Quote:
And the other point is does AL32UTF8 supports chinese characters?

Yes.

Regards
Michel
Previous Topic: DBLink after remote_login_passwordfile
Next Topic: How to remove Oracle Diagnostic pack in absence of OEM
Goto Forum:
  


Current Time: Mon Dec 05 15:07:55 CST 2016

Total time taken to generate the page: 0.14168 seconds