OraFAQ Forum: Spanish » Storing spanish data

Home » Non-English Forums » Spanish » Storing spanish data

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

Storing spanish data [message #173467]

Mon, 22 May 2006 11:03

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

Report message to a moderator

Re: Storing spanish data [message #173598 is a reply to message #173467]

Tue, 23 May 2006 06:41

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;

Report message to a moderator

Re: Storing spanish data [message #173808 is a reply to message #173598]

Wed, 24 May 2006 09:04

Maaher
Messages: 7065
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

Report message to a moderator

Re: Storing spanish data [message #174549 is a reply to message #173808]

Mon, 29 May 2006 08:54

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

Report message to a moderator

Re: Storing spanish data [message #174552 is a reply to message #174549]

Mon, 29 May 2006 09:23

Maaher
Messages: 7065
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

Report message to a moderator

Re: Storing spanish data [message #174554 is a reply to message #174552]

Mon, 29 May 2006 10:24

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

Report message to a moderator

Re: Storing spanish data [message #174743 is a reply to message #174554]

Tue, 30 May 2006 07:26

Maaher
Messages: 7065
Registered: December 2001

Senior Member

That means that the insert went wrong. The data stored is not what you wanted.

MHE

Report message to a moderator

Re: Storing spanish data [message #174797 is a reply to message #174743]

Tue, 30 May 2006 10:15

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

Report message to a moderator

Re: Storing spanish data [message #174918 is a reply to message #174797]

Wed, 31 May 2006 00:54

Maaher
Messages: 7065
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

Report message to a moderator

Re: Storing spanish data [message #174967 is a reply to message #173467]

Wed, 31 May 2006 04:37

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.

Report message to a moderator

Previous Topic:	CONOCEN algun commano en CTL para subir una data especial en Oracle
Next Topic:	OPM Administración de costos

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 17 22:29:55 CST 2025