Oracle DBA Lesson 21D — Why Text Looks Wrong in SQL*Plus

SQL*Plus does not decide what your keyboard sends. It tells Oracle what those bytes already are, in NLS_LANG. Oracle converts between that declaration and the database character set. If the declaration matches the terminal, the name survives. If it does not, a good row can look wrong, and a new insert can store the wrong characters for good.

Oracle DBA Lesson 21D Why Text Looks Wrong in SQL*Plus

Lesson 21 is the database character set: the map between characters and the bytes in the datafile. Lesson 21B is which column uses that map. Lesson 21C is character length versus byte length. This part is the client on the other end of the conversion. The videos are Lesson 21, Lesson 21B, and Lesson 21C.

The declaration is not the encoding

SQL*Plus is an OCI program. OCI reads NLS_LANG when the process starts. The value has three fields, language_territory.charset. Language picks message text and the names of days and months. Territory picks defaults such as the date format and the decimal character. Only the character set, the field after the dot, tells Oracle how to read and write character data.

That field describes the bytes the client is already producing. It does not recode the terminal, the SSH session, or the Windows code page. Setting it to the database character set does not make those bytes match. On Unix, if NLS_LANG is unset, the Oracle client uses AMERICAN_AMERICA.US7ASCII. A UTF-8 terminal plus that default is a mismatch before you have typed a name.

# What this terminal actually emits.
          locale charmap
          
          # What this process told Oracle.
          # Unset on Unix means AMERICAN_AMERICA.US7ASCII.
          printenv NLS_LANG

locale charmap prints a name such as UTF-8. That string is not an Oracle character-set name. The Oracle name for UTF-8 is AL32UTF8. ISO-8859-1 is WE8ISO8859P1. Windows Latin-1, including the euro sign, is WE8MSWIN1252. Do not paste UTF-8 after the dot. For a UTF-8 shell whose database is AL32UTF8, the honest setting is:

# Matches a UTF-8 terminal. Does not change the terminal.
          # The database character set is a separate fact.
          export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

A locale charmap of UTF-8 and a printenv of AMERICAN_AMERICA.US7ASCII is the break. The language and territory can stay AMERICAN_AMERICA. Changing them to French does not fix an encoding. The character set after the dot is the whole of this problem.

Conversion runs only when the names differ

If the character set in NLS_LANG and the database character set are the same name, Oracle does not convert. The bytes pass through. That is correct when the terminal really is that encoding. It is how a lie gets stored when the terminal is not. Oracle does not inspect the bytes to see if you told the truth.

If the names differ, Oracle converts, in both directions. On the way in, client bytes become database bytes. On the way out, database bytes become client bytes. A character the destination cannot represent is replaced. The original character is not kept beside the replacement. Once that replacement is committed, the row has changed.

Two checked cases against an AL32UTF8 database, same intended character, the two bytes of UTF-8 é or the one byte of Latin-1 :

• Terminal bytes c3,a9, declared AL32UTF8. No conversion is required. Stored bytes stay c3,a9. One character.
• Terminal byte e9, declared WE8ISO8859P1. Oracle converts Latin-1 to UTF-8. Stored bytes are again c3,a9. The declaration matched the byte, so the character survived.
• Terminal bytes c3,a9, declared WE8ISO8859P1. Oracle treats each byte as a Latin-1 character and converts those. The database stores two characters, four bytes, c3,83,c2,a9. Read back, that is not é. A later client with a correct NLS_LANG still shows the wrong characters, because they are what was written.

The other direction is the screen. The database holds a correct . NLS_LANG says US7ASCII. Oracle converts AL32UTF8 to a set that has no , and the fetch shows a replacement. The file did not change. Do not UPDATE the column to "repair" a display. You would replace a good value with the character your broken client can see.

Prove the bytes before you blame the database

DUMP and ASCIISTR return ASCII text: hex, and \ plus a Unicode code point. A US7ASCII terminal can print them even when it cannot print é. That is the test that separates a display bug from a stored value.

-- ASCIISTR \00E9 means the character is stored.
          -- \00C3\00A9 means the mis-declared UTF-8 bytes were stored.
          -- 1016 prints hex and the character set of the value.
          SELECT DUMP(name, 1016) AS stored,
                 ASCIISTR(name)   AS codepoints
                 FROM   customer_name
                 WHERE  id = :id;

Then repeat the same query from a client you trust. On Linux that is a terminal whose locale charmap is UTF-8, with NLS_LANG set to AMERICAN_AMERICA.AL32UTF8 in that process, not in some other window. If ASCIISTR shows \00E9 and this client draws the character, the first screen was the declaration. If ASCIISTR shows the two-character form, the row is already wrong. Fixing NLS_LANG stops the next insert. It does not rewrite the last one.

Instant Client uses the same variable. sqlplus, sqlldr, and the other OCI tools in that home all read NLS_LANG at startup. SQL*Loader uses it for the data file when the control file has no CHARACTERSET clause. Data Pump does not pick the dump file's encoding from NLS_LANG. The dump carries the source database character set. The variable still affects the log and the text you type on the command line. A clean expdp is not proof your interactive SQL*Plus declaration is honest.

The shell you tested is not every shell

An export in ~/.bashrc runs for an interactive bash that reads that file. It does not run for cron, for a systemd unit, for ssh host sqlplus when that command is non-interactive, or for a desktop menu that starts SQL*Plus with a nearly empty environment. Login shells read .bash_profile or .profile, which may never source .bashrc. Put the assignment in the wrapper that actually execs sqlplus, and print NLS_LANG in that same wrapper. A correct value in the window where you edited the file is not a correct value in the job.

Windows keeps a second copy. Each Oracle home can have an NLS_LANG registry value. An environment variable overrides it. The console code page (chcp) and a GUI program's ANSI code page are not the same client. A US Windows GUI is usually WE8MSWIN1252. Setting that GUI to AL32UTF8 because the database is AL32UTF8 declares UTF-8 bytes the program is not sending. Copying the database character set is the mistake in both directions: a UTF-8 terminal left on US7ASCII, and a Windows-1252 program forced to AL32UTF8.

Java is not this variable

JDBC Thin does not read NLS_LANG. A Java string is Unicode in the driver. The driver converts it to the database character set of the column, using the character set it learned from the connection. Some conversions need orai18n.jar on the classpath. An export in the application server's shell does not fix a Thin pool, and a broken pool does not mean SQL*Plus is wrong.

SQLcl is Java as well, not OCI SQL*Plus. It does not use NLS_LANG as the switch that declares its bytes. A name that looks wrong in SQL*Plus and right in SQLcl is a hint about the OCI declaration. It is not, by itself, proof the stored bytes are clean. ASCIISTR is still the proof. SQL Developer is the same family: do not set NLS_LANG and call the worksheet fixed.

Gotchas

• NLS_LANG declares the client bytes. It does not recode the terminal, and it is not the database character set. Match locale charmap, then translate that name into Oracle's name. UTF-8 becomes AL32UTF8.
• On Unix, an unset NLS_LANG is AMERICAN_AMERICA.US7ASCII. A UTF-8 SSH session with an empty variable is already the mismatch.
• Only the character set after the dot moves character data. FRENCH_FRANCE.US7ASCII is still US7ASCII.
• Identical client and database character-set names skip conversion. A false match stores the terminal's bytes as if they were already the database encoding.
• Different names cause conversion both ways. A character the target cannot hold is replaced, and the commit keeps the replacement.
• UTF-8 bytes declared as WE8ISO8859P1 into an AL32UTF8 database are stored as two characters. A correct client later still reads those two characters.
• A fetch that looks wrong while ASCIISTR shows \00E9 is a display bug. Do not update the row from the broken client.
• .bashrc is not cron, systemd, a non-interactive ssh command, or a GUI launcher. Print NLS_LANG in the process that starts SQL*Plus.
• On Windows the environment variable overrides the Oracle home registry value. The console code page and a GUI ANSI code page are different clients. WE8MSWIN1252 is the usual honest GUI declaration, not a copy of AL32UTF8.
• JDBC Thin ignores NLS_LANG. SQLcl and SQL Developer are Java. Instant Client OCI tools share the variable. Data Pump's dump encoding is the source database character set, not NLS_LANG.

Quick quiz

1. locale charmap prints UTF-8. The database character set is AL32UTF8. printenv NLS_LANG prints AMERICAN_AMERICA.US7ASCII. What is wrong?

Nothing. US7ASCII is the safe client setting for every database. NLS_LANG describes a 7-bit client. This terminal is UTF-8, so the Oracle name is AL32UTF8. The database must be recreated as US7ASCII to match the client. Change the language to FRENCH_FRANCE and leave the character set.

2. SQL*Plus shows a replacement where a name should have an accent. ASCIISTR of that column is \00E9. What do you fix?

The stored value. Update the column to the character this terminal can show. The database character set. It cannot hold the accent. The client declaration. The character is already stored. This terminal's NLS_LANG cannot represent it. The column type. Move it to NVARCHAR2.

3. An insert stored UTF-8 bytes of an accent that had been declared as WE8ISO8859P1. ASCIISTR now shows \00C3\00A9. You export a correct NLS_LANG. What happens to that row?

It stays wrong. The bad characters are what was written. New inserts can be right. Oracle repairs it on the next select, because the database is AL32UTF8. The row flips back as soon as NLS_LANG matches the database character set. Data Pump rewrites it automatically on the next backup.

4. A JDBC Thin pool inserts the wrong characters. The DBA sets NLS_LANG on the application host. What changes?

The pool. Thin drivers use NLS_LANG for every bind. The database character set, on the next connection. SQLcl only. SQL*Plus ignores NLS_LANG. Not the Thin pool. JDBC Thin ignores NLS_LANG. OCI programs such as SQL*Plus do not.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro