Dump SQL Command for NLS Debugging (Doc ID 13854.1)

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.

Scope

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

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.