Skip to Main Content
  • Questions

  • The use of extended characters in a varchar2 field (accent)

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Perez.

Asked: August 14, 2000 - 4:52 pm UTC

Last updated: August 04, 2009 - 1:54 pm UTC

Version: 7.3.3

Viewed 1000+ times

You Asked

When I introduce in a form´s varchar2 field a spanish word with an accent vocal and commit it, the result is that the accent is missing. e.g. José - Jose; camión - camion.
The same result is achieved with SQL PLUS, ORACLE NAVIGATOR, DEVELOPER 2000.
Why is this happening? How can I solve that problem?
Thanks a lot!

..............................................................

OS Client: Windows NT, 95/98
OS Server :Windows NT 4.0
SQL> /

PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_DATE_FORMAT DD-MON-YY
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET US7ASCII
NLS_SORT BINARY
NLS_CALENDAR GREGORIAN
NLS_RDBMS_VERSION 7.3.3.0.0

11 filas seleccionadas.

SQL> spool off

and Tom said...

Strange -- a database on NT would typically have the characterset WE8ISO8859P1 but the problem is now clear.

The clients have in their registry a setting of NLS_LANG. Its set to WE8ISO8859P1. This is what the client tools always put in there -- its the correct setting for Windows Clients. Its an 8 bit character set.

Your database on the other hand is US7ASCII -- a 7bit characterset. The HIGH bit is getting stripped off on the way in -- in support of the databases characterset.

Your solutions:

o use an NLS_LANG of us7ascii on the client. This will affect the behaviour of certain tools like forms and reports as well. No translation will take place and 8bit data can in fact be stored

o recreate your database with a characterset that supports 8bit data fully.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Toby, January 04, 2002 - 2:52 pm UTC

Exactly what I was looking for.

Excellent

A reader, September 18, 2003 - 7:46 am UTC

So now I can setup the application to properly accept words with accents. What about composing a query to retrieve 'similar' words, with and without the accent.
If one user enters José and the other Jose, is there a way to query for both case?



Tom Kyte
September 18, 2003 - 10:24 am UTC

Oracle text should be able to do that with stemming or fuzzy searching

</code> http://docs.oracle.com/docs/cd/B10501_01/nav/docindex.htm#index-TEX <code>

you could use convert() to conver to us7ascii, that may work as well (but would obviate an index until you build a function based one)

A reader, November 09, 2003 - 11:26 pm UTC

I have two 9.0.1.4 databases with identical nls_* parameters. Both receive input from mod_plsql web forms, via Oracle 9iAS 9.0.3. One database accepts accented characters, the other does not. Any idea what might need to be adjusted?

NAME TYPE VALUE
------------------------------------ -----------
nls_calendar string
nls_comp string
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string


Tom Kyte
November 10, 2003 - 7:21 am UTC

the app servers have different NLS_LANG settings.

one has a character set the same as the database (or at least at 8bits) and the other does not.

Spanish Accents to be downloaded to excel

Divya, August 03, 2009 - 9:07 am UTC

Hi Tom,

We are using Oracle portal to download certain data to excel from the database. Some of the fields are populated with spanish accents. After downloading to excel the spanish accents are not displaying properly in excel. Would you have any idea about this? Any help on this will be appreciated.

Thanks in Advance.
Tom Kyte
August 04, 2009 - 1:54 pm UTC

kick excel out of the loop, save the data to disk. is it correct there (debugging 101 - remove bits from the process to determine what process is causing the bad bits)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.