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

The same database text can look different in two clients. A useful first question is whether the character bytes are being interpreted correctly at the SQL*Plus client boundary, or whether the value was already changed before it reached the database. In Oracle Database 19c, command-line SQL*Plus uses Oracle Call Interface (OCI) and reads NLS_LANG from its environment when the process starts.

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

The client and database encodings

An encoding defines the rules that turn characters into bytes and interpret bytes as characters. The database character set describes how database character data is stored. The SQL*Plus client also has a character set for text it sends and displays. Oracle can convert between the client and database character sets when needed. The two settings have different jobs and can differ.

NLS_LANG has three components:

• language controls conventions such as messages and sorting.
• territory supplies conventions such as default date and number formats.
• charset declares the character set used by the client application for data processing. For command-line SQL*Plus, it should correspond to the character encoding used by the client's input and output device.

Choose the character-set component to describe the actual client input and output encoding. The database character set is a separate setting. For an OCI client, the declaration tells Oracle how to interpret client bytes. Oracle can then convert text to or from the database character set. See SQL*Plus globalization support and choosing a character set.

Follow the text in both directions

On output, a query result begins as database text. Oracle converts it for the client when required. SQL*Plus then sends the client text to its output device, which must decode those bytes correctly to display the characters.

On input, the terminal and SQL*Plus produce bytes for typed text. The declared client character set tells Oracle how to interpret those bytes before converting them for the database. If the declaration does not match the actual client/device encoding, text may be displayed incorrectly or damaged on input. The exact symptom depends on the bytes and the conversion path. An invalid byte sequence can be replaced, truncated, passed through, or reported as an error.

Suppose a correctly configured client displays it correctly while SQL*Plus displays its accent differently. The video illustrates one possible symptom as caf?. The moving accented-letter tile represents a retrieved copy of text. The stored row stays in the database. The diagram does not show a database record being moved or an observed byte sequence. A Linux check shows:

printenv NLS_LANG
locale charmap

Suppose the environment value ends in US7ASCII, while locale charmap reports UTF-8. That is a mismatch to investigate. US7ASCII is a seven-bit character set and cannot represent the accented é. locale charmap reports the process locale's character map. It does not prove the terminal emulator's actual encoding. Confirm the terminal and client configuration before deciding which Oracle client character-set value is appropriate. See language and locale preferences for client connections.

Oracle's SQL*Plus Instant Client guide gives AMERICAN_AMERICA.UTF8 as a Linux example. Oracle client character-set names identify supported Oracle encodings. Check the documentation for the installed client and its actual I/O device instead of copying a value blindly. See SQL*Plus Instant Client.

Decide which layer to investigate

Compare the same stored value through a known-good client connected to the same database and viewing the same row.

• If only one SQL*Plus terminal displays the value differently, investigate that client's NLS_LANG, process locale, terminal encoding, and client startup environment first.
• If independent clients all display the same unexpected text, verify the value and the input path that stored it. A display correction cannot restore characters already lost when data was entered.

This diagnosis applies to SQL*Plus through OCI. The NLS_LANG setting applies to OCI clients. JDBC Thin uses a separate Java globalization path. For a one-client display issue, repair the client path first. See JDBC globalization support.

Quiz

1. What does the character-set component of NLS_LANG describe for command-line SQL*Plus?

The database character set in every case. The client application's character encoding, suited to its I/O device. The column's declared length semantics. The terminal font.

2. When does command-line SQL*Plus read NLS_LANG?

Only when a query runs. When SQL*Plus starts. When a database is created. When a column is selected.

3. A single SQL*Plus terminal shows an accent incorrectly, but a known-good client shows the same stored value correctly. Where should you investigate first?

The failing client's encoding declaration and terminal path. The database character set immediately. The column's numeric format. A database restart.

4. What does locale charmap establish by itself?

The database character set. The stored bytes in a column. The process locale's character map; the terminal's actual encoding still needs confirmation. The effective NLS_LANG value.

5. Several independent clients show the same unexpected characters in the same stored value. What should you do next?

Change the database character set to match one terminal. Verify the stored value and investigate its input path. Change the SQL*Plus font. Set NLS_LANG on every client to US7ASCII.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily