I am a little doubtful that this is exactly what you are looking for, but
host echo %nls_lang%;
ENGLISH_UNITED KINGDOM.WE8ISO8859P1
shows the client nls_lang environment variable on the client.
I don't think there will be a SQL query you can run to give the 'current' setting because AFAIK the server is not aware of what translation is done client-side, so any command to show the current setting will have to be native to the client - I used SQL Developer for the above command, but I assume it will work the same in SQL*Plus
--edit
from AskTom:
only the client knows their character set as well -- it is not available "in the
database"
and
the character set describes what is stored in database.
the client makes their desired translated to character know [sic] to the database via the NLS_LANG
settting.
If you were on 11.1+, you might have some joy with v$session_connect_info, because:
This information is pushed by OCI to the server ats login time.
But I discovered it would still depend on how you are connecting, eg from the JDBC Thin Driver you aren't using OCI and so the information isn't pushed
The reason for the truncation is quite simple. Some characters (accented ones, for example) in the WE8ISO8859P1 character set are stored as a single byte, but in AL32UTF8 they end up being stored as multiple bytes. As a result of conversion, a 4000 character string may end up actually requiring more than 4000 bytes.
By way of example, this query shows you that the Euro symbol (0x80 in WE8ISO8859P1) becomes 2 bytes in AL32UTF8:
SQL> select length(convert(chr(128),'AL32UTF8','WE8ISO8859P1')) from dual
2 /
LENGTH(CONVERT(CHR(128),'AL32UTF8','WE8ISO8859P1'))
---------------------------------------------------
2
SQL>
To list all characters that will be affected by the change, you can use the following query:
with n as
(
select level as c from dual
connect by level <= 255
)
select c as "WE8ISO8859P1 value",
'"'||chr(c)||'"' as Character,
length(convert(chr(c),'AL32UTF8','WE8ISO8859P1')) as "New length"
from n
where length(convert(chr(c),'AL32UTF8','WE8ISO8859P1'))>1;
Unfortunately the maximum length a CHAR
or VARCHAR
string can be in Oracle is 4000 bytes. The only option available to you if characterset conversion pushes you over this limit is to convert the columns to use the CLOB
datatype, but we warned - CLOB
s are difficult to deal with and can present challenges.
Best Answer
HSQLDB stores text data as Unicode. You can verify that by opening the
.script
file for the database and looking at the data.For example, for a table with a row containing "Montréal" the
.script
file containswhere
\u0039
is the Unicode code point for the letteré
.