I've looked everywhere for a solution and can't find one. We have
french descriptions in our Oracle data warehouse that I need to use if
the client is flag as speaking french. Through SQL/PLUS I can see the
umlauts (accents) like the letter é. But MS Access mixes them up with
another character. I have set my computers language to
English(Canadian) and ever French(Canadian), nothing changes. I will
not parse every word to test for the characters because the filed is
too long and the recordset large.

I have cross posted this to the MS Access discussion group, but thought
that one of you Oracle DBAs may have came across such a problem with
your users in a MS Access envirnment.

Any ideas how I can configure this?

Alt255 wrote:
> I've looked everywhere for a solution and can't find one. We have
> french descriptions in our Oracle data warehouse that I need to use if
> the client is flag as speaking french. Through SQL/PLUS I can see the
> umlauts (accents) like the letter é. But MS Access mixes them up with
> another character. I have set my computers language to
> English(Canadian) and ever French(Canadian), nothing changes. I will
> not parse every word to test for the characters because the filed is
> too long and the recordset large.
>
> I have cross posted this to the MS Access discussion group, but thought
> that one of you Oracle DBAs may have came across such a problem with
> your users in a MS Access envirnment.
>
> Any ideas how I can configure this?

I would guess you need to either configure Access to use the proper
codepage " A conversion table that specifies how characters are encoded
(usually used for servers)" or change the data warehouse to use the
characterset that your Access codepage is set to. What is your
database set to? What is Access set to?

select * from nls_database_parameters;

You might need to look in your registry for something like
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Contro l\Nls\CodePage\
or maybe something Access specific, I wouldn't know.

If you have access to metalink, see Note:158577.1 for a character set
faq.

Note the versions and platforms of everything are necessary to properly
answer your question.

jg
--
@home.com is bogus. "We're not mining or trolling through the
personal lives of millions of innocent Americans. Our efforts are
focused on links to al-Qaeda and their known affiliates." - George
H. W. Bush. OK then, let me access the telephone number db so I can
find out which #$%#@ organization is collecting my phone number with
those hangup calls for later machine dialing.

I'm starting to think this is the issue -
Here are the nls settings
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT MM/DD/YYYY
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 TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_NCHAR_CHARACTERSET US7ASCII
NLS_RDBMS_VERSION 8.1.7.3.0


in the registry the NLS_Lang in ..ORACLE/Home0 =
AMERICAN_AMERICA.US7ASCII

I tried a pass-through in Excel and the characters aren't represented
properly either. Impromptu displays the umlauts perfectly fine.

Alt255 schreef:
> I'm starting to think this is the issue -
> Here are the nls settings
> PARAMETER VALUE
> ------------------------------ ----------------------------------------
> NLS_LANGUAGE AMERICAN
> NLS_TERRITORY AMERICA
> NLS_CURRENCY $
> NLS_ISO_CURRENCY AMERICA
> NLS_NUMERIC_CHARACTERS .,
> NLS_CHARACTERSET US7ASCII

How in the world do you expect 7 bits to hold 8 bit data?!?
You are using the extended ASCII characterset (ASCII, being
short sighted american, does not allow accepted charactes),
meaning the full 8 bits of a byte.

The fact you have a front end that inserts and retrieves them
correctly means 2 characters are used in the database, some
other codeing mechanism, or you simply are lucky.

My advise: if you're working in the western world, with clients
that predominantly use Microsoft based O/S's for the forn end,
choose WE8MSWIN1252 for database characterset, as well as
client characterset!
Some clients may need WE8MSWIN1254, or another MSWIN codepage,
but 99.9% of the times this setup works. Again, make sure, that
at least the client side installation uses MSWIN1252!
Database is less important than client (7bits, 8bits or multi
byte issues left alone)
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

On Sat, 13 May 2006 17:57:25 +0200, Frank van Bortel
<frank.van.bortel@gmail.com> wrote:

>The fact you have a front end that inserts and retrieves them
>correctly means 2 characters are used in the database, some
>other codeing mechanism, or you simply are lucky

if the client NLS_LANG is on 7-bit AND the server there is no
conversion, so 8 bit characters will be stored.
Sometimes stupidity pays.

--
Sybrand Bakker, Senior Oracle DBA

"Sybrand Bakker" <postbus@sybrandb.demon.nl> wrote in message
news:3k5c625h5p1gsfhq2qi8u93mf76nvf02rq@4ax.com...
> On Sat, 13 May 2006 17:57:25 +0200, Frank van Bortel
> <frank.van.bortel@gmail.com> wrote:
>
> >The fact you have a front end that inserts and retrieves them
> >correctly means 2 characters are used in the database, some
> >other codeing mechanism, or you simply are lucky
>
> if the client NLS_LANG is on 7-bit AND the server there is no
> conversion, so 8 bit characters will be stored.
> Sometimes stupidity pays.
>
> --
> Sybrand Bakker, Senior Oracle DBA
Sybrand is correct. If the client and the server character sets are the
same then Oracle doesn't check what you are putting in. That sounds like a
good idea, but it causes a lot of other problems. (Unfortunately, it is the
advice a particular 3 letter large European Application Vendor tells their
customers to use.) The "solution" "works" but if you want to interface with
another system it will wreck havoc. Also if you have another client that
doesn't display the character set it will look like goboldy gook.

For example, lets assume you put certain European characters into your RDBMS
using this ASCII 7 to ASCII 7 kludge. Later your ETL tool comes around and
wants to extract the data to your UTF8 compliant Oracle database which is
your data warehouse. Now some of those European characters will not
translate. They will be ? or other things. Why? Some of these European
characters will be over ASCII 128 and so Oracle will convert the character
from ASCII 7 to utf 8. Since in an ASCII 7 bit database you can't have
characters over ASCII 128 Oracle will subtract or wrap around so the ASCII
code is <129. Thus in UTF8 you will end up with garbage.

Magnify this by clients putting all sorts of ASCII codes mapping to all
sorts of client codes. Now you will have a heck of a time translating these
characters to the correct representation. (eg Some clients forcing in
European characters - western and eastern, and Asian - shift JIS)

A mess, a real mess.
Jim

Sybrand Bakker schreef:
> On Sat, 13 May 2006 17:57:25 +0200, Frank van Bortel
> <frank.van.bortel@gmail.com> wrote:
>
>> The fact you have a front end that inserts and retrieves them
>> correctly means 2 characters are used in the database, some
>> other codeing mechanism, or you simply are lucky
>
> if the client NLS_LANG is on 7-bit AND the server there is no
> conversion, so 8 bit characters will be stored.
> Sometimes stupidity pays.
>
> --
> Sybrand Bakker, Senior Oracle DBA

Only if the db is created with an 8 bit characterset.
Which -upon reflection- it must be (otherwise, NO extended
character would ever be shown...).

I stand corrected
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

"Frank van Bortel" <frank.van.bortel@gmail.com> wrote in message
news:e47g1d$ra8$2@news4.zwoll1.ov.home.nl...
> Sybrand Bakker schreef:
> > On Sat, 13 May 2006 17:57:25 +0200, Frank van Bortel
> > <frank.van.bortel@gmail.com> wrote:
> >
> >> The fact you have a front end that inserts and retrieves them
> >> correctly means 2 characters are used in the database, some
> >> other codeing mechanism, or you simply are lucky
> >
> > if the client NLS_LANG is on 7-bit AND the server there is no
> > conversion, so 8 bit characters will be stored.
> > Sometimes stupidity pays.
> >
> > --
> > Sybrand Bakker, Senior Oracle DBA
>
> Only if the db is created with an 8 bit characterset.
> Which -upon reflection- it must be (otherwise, NO extended
> character would ever be shown...).
>
> I stand corrected
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...

Frank,
If you search metalink you will find an article that says that if the
client's character setting and the database's character setting are = then
the data will not be checked for character set conversion. So yes you can
put an 8 bit character in a 7 bit database. (I have one US7bit ASCII that
has 8 bit characters) I know it sounds controdictory, but a certain large
European application vendor actually tells their customers to set up their
Oracle databases that way. (US 7 bit ASCII) Yes, the recommendation sucks
and causes a lot of problem with other systems. However, the large European
application vendor doesn't consider that there would be any reason why you
should use an outside system. (I think you can guess what the vendor's name
is.)
Jim

Thanks for the input guys. It's a little over my head. I changed
nls_lang=WE8MSWIN1254 but the connection failed because it considers it
invalid.. Does anyone know what I need to configure?

On 14 May 2006 10:50:27 -0700, "Alt255" <alt255.2005@gmail.com> wrote:

>Thanks for the input guys. It's a little over my head. I changed
>nls_lang=WE8MSWIN1254 but the connection failed because it considers it
>invalid.. Does anyone know what I need to configure?

nls_lang consists of three parts
<language>_<territory>.<characterset>

For the first two you can safely use American_America

so nls_lang becomes

American_America.WE8MSWIN1254

If that doesn't work, remove NLS_LANG
connect and query
v$nls_valid_values

This will reflect your actual installation

Hth


--
Sybrand Bakker, Senior Oracle DBA