I'm setting up Oracle libraries in a Windows Server box so two PHP web
sites can access a remote Oracle 10g database. I've downloaded the
Instant Client *.zip package and everything works fine so far, except
for some charset issues (Spanish letters do not show properly). Setting
NLS_LANG environmental variable seems to be the clue but I don't know
which value I should use. Given that data is in Spanish I have three
likely candidates:

SPANISH_SPAIN.WE8MSWIN1252 (the Windows codepage)
SPANISH_SPAIN.WE8ISO8859P1 (ISO-Latin-1, used in first site)
SPANISH_SPAIN.WE8ISO8859P15 (ISO-Latin-9, used in second site)

From the "Oracle Database Globalization Support Guide" I understand
that NLS_LANG makes Oracle perform a charset conversion. Since both
sites use different charsets and I can convert in my PHP app when
necessary, I think it'd be a good idea that Oracle does not make any
conversion. But I don't know how to find out the charset that database
tables are using natively. How could I find out? Does all this make any
sense?


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

On Wed, 23 Jul 2008 14:29:20 +0200, "Álvaro G. Vicario"
<alvaroNOSPAMTHANKS (AT) demogracia (DOT) com> wrote:

You could find out by querying NLS_DATABASE_PARAMETERS.
However, for Windows the default is MSWIN1252.
WE8ISO8859P1 does not contain the euro sign.

--

Sybrand Bakker
Senior Oracle DBA

On Wed, 23 Jul 2008 14:29:20 +0200, "Álvaro G. Vicario"
<alvaroNOSPAMTHANKS (AT) demogracia (DOT) com> wrote:

You could find out by querying NLS_DATABASE_PARAMETERS.
However, for Windows the default is MSWIN1252.
WE8ISO8859P1 does not contain the euro sign.

--

Sybrand Bakker
Senior Oracle DBA

On Wed, 23 Jul 2008 14:29:20 +0200, "Álvaro G. Vicario"
<alvaroNOSPAMTHANKS (AT) demogracia (DOT) com> wrote:

You could find out by querying NLS_DATABASE_PARAMETERS.
However, for Windows the default is MSWIN1252.
WE8ISO8859P1 does not contain the euro sign.

--

Sybrand Bakker
Senior Oracle DBA

On Wed, 23 Jul 2008 14:29:20 +0200, "Álvaro G. Vicario"
<alvaroNOSPAMTHANKS (AT) demogracia (DOT) com> wrote:

You could find out by querying NLS_DATABASE_PARAMETERS.
However, for Windows the default is MSWIN1252.
WE8ISO8859P1 does not contain the euro sign.

--

Sybrand Bakker
Senior Oracle DBA

sybrandb (AT) hccnet (DOT) nl escribió:
A query to that table shows that NLS_CHARACTERSET is WE8ISO8859P1 so
I'll work on that base.

You are right (I've seen apps that use the currency symbol instead).
Luckily it's not really an issue in web sites since you can always use
the &euro; entity--I just need to know what charset the data is actually
using.

Thank you very much.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

sybrandb (AT) hccnet (DOT) nl escribió:
A query to that table shows that NLS_CHARACTERSET is WE8ISO8859P1 so
I'll work on that base.

You are right (I've seen apps that use the currency symbol instead).
Luckily it's not really an issue in web sites since you can always use
the &euro; entity--I just need to know what charset the data is actually
using.

Thank you very much.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

sybrandb (AT) hccnet (DOT) nl escribió:
A query to that table shows that NLS_CHARACTERSET is WE8ISO8859P1 so
I'll work on that base.

You are right (I've seen apps that use the currency symbol instead).
Luckily it's not really an issue in web sites since you can always use
the &euro; entity--I just need to know what charset the data is actually
using.

Thank you very much.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

sybrandb (AT) hccnet (DOT) nl escribió:
A query to that table shows that NLS_CHARACTERSET is WE8ISO8859P1 so
I'll work on that base.

You are right (I've seen apps that use the currency symbol instead).
Luckily it's not really an issue in web sites since you can always use
the &euro; entity--I just need to know what charset the data is actually
using.

Thank you very much.


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

"Álvaro G. Vicario" <alvaroNOSPAMTHANKS (AT) demogracia (DOT) com> wrote:
If you really plan to use WE8ISO8859P1 on the Windows client, please write
your application so that it makes dead sure that only LATIN-1 characters
are entered into the database.

Oracle has a special "feature" in that it will not check your input for
validity if client and server character sets are the same.
This means that your client application can enter arbitrary junk into the
database that will be happily stored as is.

Let me give you an example.
If the PHP application decides to store an Euro sign in the database and
it happens to run in the Windows codepage, it will try to store a byte 0x80.
Oracle will happily store that byte, even though this byte does not make
any sense in LATIN-1. You will not notice that until you try to access the
database with a different client character set, e.g. from Java.
Then Oracle will return garbage instead of the Euro signs, and there is
no way to fix that.

But even if you plan to use a different character set on the Windows client
(which I personally would do, namely WE8MSWIN1252), you will not get error
messages if invalid characters are stored. Oracle will check the characters,
but if it detects - say - a Euro sign, it will not throw an error but
clandestinely convert the character to a question mark.


The best solution in my opinion would be to recreate the database using the
character set AL32UTF8 and on the client either use WE8MSWIN1252 or
AL32UTF8 (if your PHP code speaks UTF-8).

Yours,
Laurenz Albe