Overview
The goal is to insert Unicode data into an Oracle Database from a client machine without data corruption. Oracle Database has a Unicode AL32UTF8 Character Set that allows the insert of special characters without using any additional data types.
Understand the Character Set Flow
When you connect via SQL*Plus (or any OCI client), Oracle translates between:
- Database character set (ideal is AL32UTF8, which supports all Unicode)
- Client character set (controlled by NLS_LANG and the terminal encoding).
If the client character set is wrong, Oracle will assume your input is in that set and try to convert it to UTF-8 — which can mangle Unicode. Be aware that if you are using SQL Developer, it is a self contained tool and does not use the Windows Registry or Environment variables, however in most cases it will be set correctly to allow Unicode data to be inserted. Look under Tools / Preferences / NLS to check the settings.
Configure the Client Machine
Verify the Database Character Set by running:
SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
AL32UTF8
Set the Client Environment to match Terminal Encoding
NLS_LANG needs to reflect the client OS language, territory, and encoding. For Unicode to work with AL32UTF8 databases, CLIENT_CHARSET should be AL32UTF8 as part of the following format:
NLS_LANG=[LANGUAGE]_[TERRITORY].[CLIENT_CHARSET]
An example for Linux Bash:
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
For Windows, open a CMD prompt:
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
To make this permanent in Windows, set NLS_LANG as a system environment variable.
NB: With matching Character Sets both set to AL32UTF8, the client just sends bytes straight to the database with no intermediate conversion.
Use a UTF-8 compatible SQL Script Editor
If you’re inserting data via scripts, then make sure that you save files in UTF-8 encoding without BOM (Byte Order Mark).
Most modern text editors (VS Code, Notepad++, Sublime) can do this.
Test the configuration
Run the Insert using SQL*Plus, and check the results in the Database.
Insert into PLACES_TABLE (PLACE_NAME) values ('Kaikōura');
1 row created.
commit;