Dump SQL Command for NLS Debugging
(Doc ID 13854.1)
Last updated on OCTOBER 08, 2019
Applies to:Oracle Database - Enterprise Edition - Version 22.214.171.124 to 126.96.36.199 [Release 8.0.3 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 07-Dec-2016***
This note looks at the sql DUMP function and especially its usage in debugging NLS problems. The first part of the note focuses on using DUMP on CHAR, VARCHAR2, NCHAR and NVARCHAR2 columns. In 2 sections at the end of the note we will also discuss how to use DUMP on DATE and NUMBER columns, and how to interpret the output of DUMP on those columns.
The dump command cannot be used on CLOB or NCLOB columns, you can however to a TO_CHAR (or TO_NCHAR) first.
When providing DUMP results in a SR please do use the dump(<column>,1016) syntax.
The DUMP sql command can be very useful in debugging issues related to how the data is stored in the database.
There is however a easier way when checking data stored in CHAR or VARCHAR2 columns:
A very easy way to see if your current NLS_CHARACTERSET supports/defines the character you try to insert is to use an Unicode client like iSqlPlus (<Note 231231.1> and <Note 281847.1>) or SqlDeveloper which you can download here.
If you can insert the character(s) in SqlDeveloper in a test table then your current NLS_CHARACTERSET defines the character you try to insert.
If you CANNOT insert the character in SqlDeveloper in a test table then your current NLS_CHARACTERSET does NOT defines the character you try to insert. In that case you need to change the NLS_CHARACTERSET. <Note 225912.1> Changing the Database Character Set ( NLS_CHARACTERSET )
If NEW data can be inserted in a test table but EXISTING data is NOT showing up correctly in Sqldeveloper then you have "garbage" data in your database. Then you can use the dump command to see the actual codepoints stored. A tool that may be of use combined (!) with the dump command is Csscan.
See the "lossy" section in <Note 444701.1> Csscan output explained
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document
|Basic DUMP usage for character data|
|DUMP usage for NUMBER datatype|
|DUMP usage for DATE datatype|