Home » Non-English Forums » Spanish » Storing spanish data
Storing spanish data [message #173467] Mon, 22 May 2006 11:03 Go to next message
Aju
Messages: 94
Registered: October 2004
Member
Hi
I have come across an issue on multilingual characters. Please let me know of any input.

I have updated a Spanish data as below
update x set value='Podéis'

But when I see from DB it is showing me Podiis


May I know is it the expected behavior.

Anyway the statement "SELECT CONVERT('Podéis','UTF8', 'WE8ISO8859P1') FROM dual" shows Podiis.

Is it the expected behavior and the front using the table need to convert to Spanish to show the data or I need to do something (May be covert to Spanish by any means) to store the data as Spanish.

Thanks
SQL> SELECT * from NLS_SESSION_PARAMETERS;

PARAMETER VALUE
------------------------------------------------------------------------------------------ ---------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSX
NLS_TIMESTAMP_FORMAT DD-MON-RR
NLS_TIME_TZ_FORMAT HH.MI.SSX
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Re: Storing spanish data [message #173598 is a reply to message #173467] Tue, 23 May 2006 06:41 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
One intersting stuff. When I paste the following letters in TOAD or SQLPLUS "Пожалуйста оцените эту Страницу!"(Probably these are spanish letters), it gets converted to
?????????? ??????? ??? ????????!

Can someone explain me what could be the reason. Also I tried with the following parameter change but it does not help.

ALTER SESSION SET NLS_LANGUAGE = SPANISH NLS_TERRITORY = SPAIN;

Re: Storing spanish data [message #173808 is a reply to message #173598] Wed, 24 May 2006 09:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It is possible that SQL*plus just can't display it, have you tried to take a DUMP from the field as well? That way, you can verify the ASCII content.

MHE
Re: Storing spanish data [message #174549 is a reply to message #173808] Mon, 29 May 2006 08:54 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
I tired that but the result is the same. Even I have tried to import the data from excel file using TOAD. Even that failed as well.

My be I need to try from the external file.

Thanks Maheer
Re: Storing spanish data [message #174552 is a reply to message #174549] Mon, 29 May 2006 09:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
What do you mean with 'FAILED': do a
select dump(yourfield) from yourtable
and verify the ascii values of the content. It is very well possible that SQL*plus just can't display it. A dump() rarely fails. It is just to verify that Oracle has correctly inserted the values.

MHE
Re: Storing spanish data [message #174554 is a reply to message #174552] Mon, 29 May 2006 10:24 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
I am not sure how this dump behaves.

When I did

INSERT INTO F VALUES('Podéis');

INSERT INTO F VALUES('Podéis','UTF8', 'WE8ISO8859P1');

And retrieved the data I got the following output

Podiis
?????????? ??????? ??? ????????!"(

And the respective dump shows

Typ=1 Len=6: 80,111,100,105,105,115
Typ=1 Len=34: 63,63,63,63,63,63,63,63,63,63,32,63,63,63,63,63,63,63,32,63,63,63,32,63,63,63,63,63,63,63,63,33,34,40

I am not sure how to interpret this data. But my frontend user is saying this is not the correct spanish data.

Thanks for your time
Re: Storing spanish data [message #174743 is a reply to message #174554] Tue, 30 May 2006 07:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That means that the insert went wrong. The data stored is not what you wanted.

MHE
Re: Storing spanish data [message #174797 is a reply to message #174743] Tue, 30 May 2006 10:15 Go to previous messageGo to next message
Aju
Messages: 94
Registered: October 2004
Member
Yes this is the problem. To over come this I tried to access the file through external table(This is the first time I would be using this). It threw me an error. Dear Maheer do you have any input to this.

Thanks

create or replace directory ext_dir as '/home/cmsqa/test';

create table ext_table_csv (
s Varchar2(2000)
)
organization external (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields terminated by ','
)
location ('test1')
)
reject limit unlimited;


SQL> SELECT * FROM ext_table_csv;
SELECT * FROM ext_table_csv
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXT_TABLE_CSV_492510.log
OS error No such file or directory
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
Re: Storing spanish data [message #174918 is a reply to message #174797] Wed, 31 May 2006 00:54 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The OS error is clear 'No such file or directory'. Check whether you have entered the correct directory and that the file exists in the directory. Check whether Oracle has OS permissions to read from the directory.

What does this query tell you:
select * from dba_directories;


MHE
Re: Storing spanish data [message #174967 is a reply to message #173467] Wed, 31 May 2006 04:37 Go to previous message
Aju
Messages: 94
Registered: October 2004
Member
Thanks for pointing this. I cross checked with the file system and could able to get the correct path.

create or replace directory ext_dir as
'/wbes-home/cmsqa/test';

One intersting finding on the data for same insert for 'Podéis'

Pod? -- When inserted through external file from unix box
Pod☻is -- When inserted through command prompt from NT (and then connecting thourgh SQL plus)
Podiis -- When inserted through SQLPLUS or TOAD in NT

I believe the one with the external file is the correct one. I do not know the way to recognise a spanish character from DB, would appreciate if anyone can guide on this. I have asked for the fronend users to verify on the same.
Previous Topic: CONOCEN algun commano en CTL para subir una data especial en Oracle
Next Topic: OPM Administración de costos
Goto Forum:
  


Current Time: Thu Oct 29 21:28:31 CDT 2020