Oracle Character Set Conversion (2): 3. Configuring the Client Environment (1)

Examine Oracle Character Set conversion client environment configuration. Server Character Set and Client NLS_LANG settings can be divided into 9 test cases and the results of each test can be checked.

This is a continuation of the previous article.

Oracle Character Set Conversion(1): 1. Necessity, Correct Oracle Character Set Setup Guide

3. Oracle Character Set Conversion Client Environment Configuration

The environment variable for setting the character set in Oracle Client is NLS_LANG.

* Reference Oracle documentation: Choosing a Locale with the NLS_LANG Environment Variable (oracle.com)

Commonly, it is thought that the Client NLS_LANG must match the Character Set of the Server, but this is not mandatory. Server Character Set is a setting for “storing” character string data, and Client NLS_LANG is a setting for “showing” and “sending” character string data.

In other words, if Server Character Set is incorrectly set, string data is stored incorrectly, and if Client NLS_LANG is incorrectly set, string data is incorrectly displayed and transmitted incorrectly.

Define the case of Server Character Set and Client NLS_LANG settings as follows and look at the test results.

Case #	Server Character Set	Client NLS_LANG
1	US7ASCII	AMERICAN_AMERICA.KO16KSC5601
2	US7ASCII	AMERICAN_AMERICA.KO16MSWIN949
3	US7ASCII	AMERICAN_AMERICA.US7ASCII
4	KO16MSWIN949	AMERICAN_AMERICA.KO16KSC5601
5	KO16MSWIN949	AMERICAN_AMERICA.KO16MSWIN949
6	KO16MSWIN949	AMERICAN_AMERICA.US7ASCII
7	AL32UTF8	AMERICAN_AMERICA.KO16KSC5601
8	AL32UTF8	AMERICAN_AMERICA.KO16MSWIN949
9	AL32UTF8	AMERICAN_AMERICA.AL32UTF8

To test the server character set differently, three Oracle instances were configured on the local PC and the TNS names were designated as oraus7, oramswin949, orautf, respectively.

• oraus7: US7ASCII
• oramswin949: KO16MSWIN949
• orautf: AL32UTF8

Two input values for each test case were selected: “Hangul” and “Sharp”.

“Hangul” is to check whether general Korean input/output is possible, and “sharp” is to check the input/output of characters not supported by KSC5601.

The test script (example) and explanation are as follows.

-- Windows command prompt 환경
title Test Case (1) US7ASCII, AMERICAN_AMERICA.KO16KSC5601
set NLS_LANG=AMERICAN_AMERICA.KO16KSC5601
sqlplus -S leg/leg@oraus7

-- sqlplus 환경
TRUNCATE TABLE Z_TEST;
INSERT INTO Z_TEST(VAL) VALUES('한글');
INSERT INTO Z_TEST(VAL) VALUES('샾');
COMMIT;
SELECT VAL, DUMP(VAL, 1016) DP FROM Z_TEST;
EXIT;

• Line 2: Set the title of the Command prompt window for easy identification (different for each test case, see below)
• Line 3: Set NLS_LANG (different for each test case, see below)
• Line 4: Execute and connect to sqlplus (Set up differently for each test case. See below)
• Line 7: Truncate the test table (Z_TEST)
• Line 8: Enter the value “Hangul”
• Line 9: Enter the “sharp” value
• Line 10: Run COMMIT
• Line 11: Output the input values and the result of the DUMP function
  • Note: If you set the second parameter of the DUMP function to 1016, you can check the length of the stored value (number of bytes), character set, and the hexadecimal value of each byte.
• Line 12: end

3.1. Results by test case

3.1.1. Case #1 (US7ASCII – KO16KSC5601)

• Server Character Set: US7ASCII (sqlplus -S leg/leg@us7ascii)
• Client NLS_LANG: AMERICAN_AMERICA.KO16KSC5601
• Execution result
  • 'Hangul': input succeeded, output failed (output as a question mark (??))
  • 'sharp': input failed

3.1.2. Case #2 (US7ASCII – KO16MSWIN949)

• Server Character Set: US7ASCII (sqlplus -S leg/leg@us7ascii)
• Client NLS_LANG: AMERICAN_AMERICA.KO16MSWIN949
• Execution result
  • 'Hangul': input succeeded, output failed (output as a question mark (??))
  • 'Sharp': input succeeds, output fails (outputs as a question mark (?))

3.1.3. Case #3 (US7ASCII – US7ASCII)

• Server Character Set: US7ASCII (sqlplus -S leg/leg@us7ascii)
• Client NLS_LANG: AMERICAN_AMERICA.US7ASCII
• Execution result
  • 'Hangul': Successful input and output (it is stored incorrectly)
  • 'Sharp': Successful input and output (it is stored incorrectly)

3.1.4. Case #4 (KO16MSWIN949 – KO16KSC5601)

• Server Character Set: KO16MSWIN949 (sqlplus -S leg/leg@oramswin949)
• Client NLS_LANG: AMERICAN_AMERICA.KO16KSC5601
• Execution result
  • 'Hangul': input, output successful
  • 'sharp': input failed

3.1.5. Case #5 (KO16MSWIN949 – KO16MSWIN949)

• Server Character Set: KO16MSWIN949 (sqlplus -S leg/leg@oramswin949)
• Client NLS_LANG: AMERICAN_AMERICA.KO16MSWIN949
• Execution result
  • 'Hangul': input, output successful
  • 'sharp': input, output successful

3.1.6. Case #6 (KO16MSWIN949 – US7ASCII)

• Server Character Set: KO16MSWIN949 (sqlplus -S leg/leg@oramswin949)
• Client NLS_LANG: AMERICAN_AMERICA.US7ASCII
• Execution result
  • 'Hangul': input succeeded, output failed (output as a question mark (????))
  • 'sharp': input succeeded, output failed (output as a question mark (??))

3.1.7. Case #7 (AL32UTF8 – KO16KSC5601)

• Server Character Set: AL32UTF8 (sqlplus -S leg/leg@orautf)
• Client NLS_LANG: AMERICAN_AMERICA.KO16KSC5601
• Execution result
  • 'Hangul': input, output successful
  • 'sharp': input failed

3.1.8. Case #8 (AL32UTF8 – KO16MSWIN949)

• Server Character Set: AL32UTF8 (sqlplus -S leg/leg@orautf)
• Client NLS_LANG: AMERICAN_AMERICA.KO16MSWIN949
• Execution result
  • 'Hangul': input, output successful
  • 'sharp': input, output successful

3.1.9. Case #9-1 (AL32UTF8 – AL32UTF8)

• Server Character Set: AL32UTF8 (sqlplus -S leg/leg@orautf)
• Client NLS_LANG: AMERICAN_AMERICA.AL32UTF8
• Execution result
  • 'Hangul': input, output successful
  • 'Shop': Input failure: The default code page of the Windows command prompt (cmd.exe) is 949 (ANSI), and it does not support UTF-8 input/output, so it does not run normally. If you execute it with PowerShell supporting UTF-8 input/output (Case #9-2), you can see that the input/output works normally.

3.1.10. Case #9-2 (AL32UTF8 – AL32UTF8) – UTF-8 Support Running with PowerShell

• Server Character Set: AL32UTF8 (sqlplus -S leg/leg@orautf)
• Client NLS_LANG: AMERICAN_AMERICA.AL32UTF8
• Execution result
  • 'Hangul': input, output successful
  • 'sharp': input, output successful

---

Up to this point, we have looked at the configuration of the Oracle Character Set conversion client environment and the results of each test case. In the next article, we will summarize these results and look at the recommended configuration.