A datafile does not store the letters you see. It stores bytes. The database character set is the map Oracle uses in both directions: from a character such as the é in José to those bytes, and from those bytes back to José. A character set that has no é cannot store that spelling.
Oracle DBA Lesson 21 — Why a Database Needs a Character Set
The file holds bytes, not letters
José is four characters. Café is four characters. The accent is not decoration on a base letter. é is a character of its own, U+00E9. The datafile has bytes, and the database character set decides which bytes mean that character.
The character set is the encoding for CHAR, VARCHAR2, CLOB, and LONG. It is also the encoding of SQL and PL/SQL source stored in the dictionary. RAW and BLOB are not in this map.
What you ask, and what it is allowed to say
The character set is chosen in CREATE DATABASE and stored with the database. It is not an instance parameter, and ALTER SESSION cannot set it. NLS language, territory, and date format settings do not change how José is stored.
SELECT parameter, value
FROM nls_database_parameters
WHERE parameter LIKE '%CHARACTERSET%'
ORDER BY parameter;On a current database this returns NLS_CHARACTERSET = AL32UTF8 and NLS_NCHAR_CHARACTERSET = AL16UTF16. On AL32UTF8, é is two bytes: c3,a9. José is five bytes.
AL32UTF8 is not the character set named UTF8
For a new database, AL32UTF8 is Oracle's Unicode database character set. ASCII letters stay one byte; accents are two bytes and supplementary characters can be four. The Oracle character set named UTF8 is a different, older encoding. The create clause you want is CHARACTER SET AL32UTF8.
NCHAR, NVARCHAR2, and NCLOB use the national character set. That set does not change ordinary VARCHAR2. A literal without the N prefix is still the database character set.
Five bytes do not fit in four
The default length of VARCHAR2(n) and CHAR(n) is bytes. VARCHAR2(3 BYTE) rejects ééé with ORA-12899 because the value is 6 bytes. VARCHAR2(3 CHAR) stores three characters and six bytes. CHAR semantics still hit the type's byte maximum.
CREATE TABLE customer_name (
byte_value VARCHAR2(3 BYTE),
char_value VARCHAR2(3 CHAR)
);
SELECT column_name, data_type, data_length, char_length, char_used
FROM user_tab_columns WHERE table_name = 'CUSTOMER_NAME';The client declares a second character set
NLS_LANG declares the client bytes for OCI clients such as SQL*Plus. It has the form language_territory.characterset, for example AMERICAN_AMERICA.AL32UTF8. It does not recode the terminal or change the database character set.
If the declaration and database character set differ, Oracle converts. A destination that cannot represent a character substitutes it. If UTF-8 bytes c3,a9 are falsely declared as WE8ISO8859P1, the stored characters become é, not é.
Do not relabel the bytes
A wrong database character set is a migration. Oracle's supported path to AL32UTF8 is Database Migration Assistant for Unicode after scanning the data. ALTER DATABASE CHARACTER SET and editing the dictionary row are not a conversion.
Gotchas
- The datafile stores bytes; the database character set is the map back to characters.
US7ASCIIcannot storeé. Conversion substitutes a replacement.AL32UTF8is not the same encoding as Oracle's olderUTF8.NCHARandNVARCHAR2use the national character set and do not changeVARCHAR2.NLS_LENGTH_SEMANTICSdefaults to BYTE; state BYTE or CHAR on the column.NLS_LANGdescribes client bytes; equal declarations skip conversion only when true.- SQL*Loader, Data Pump, and JDBC use different conversion paths.
Quick quiz
What tells Oracle how to represent a stored name as bytes?
The database character set.
What happens inserting ééé into VARCHAR2(3 BYTE) on AL32UTF8?
ORA-12899: the value is 6 bytes and the column allows 3.
Does a matching NLS_LANG declaration always make bad bytes safe?
No. Conversion is skipped, so a false declaration can be stored as-is.
No comments:
Post a Comment