Oracle DUMP: Data Type Code, Bytes, Hex, and Character Set

Oracle DUMP() returns a VARCHAR2 that describes the internal representation of a value.

The result can include:

• Oracle’s internal data type code
• the value’s length in bytes
• the bytes that represent the value
• optionally, the character set name

This makes DUMP() useful when debugging character-set, datatype, and hidden-byte problems.

Syntax

DUMP(
  expr
  [, return_fmt
    [, start_position
      [, length]
    ]
  ]
)

Return Formats

The return_fmt argument controls how each byte is displayed:

Decimal (10) is the default.

To include the character-set name, add 1000 to the format value. Common examples are 1010 and 1016.

Basic Example

SELECT DUMP('ABC')
FROM dual;

A typical result is:

Typ=96 Len=3: 65,66,67

Here:

• Typ=96 is Oracle’s internal type code for the expression
• Len=3 is the byte length
• 65,66,67 are the decimal byte values for A, B, and C in ASCII-compatible character sets

Hexadecimal Output

SELECT DUMP('ABC', 16)
FROM dual;

Typical byte output:

41,42,43

Hex output is often easier to use when looking for invisible or unexpected bytes.

Show the Character Set

Add 1000 to the return format:

SELECT DUMP('ABC', 1016)
FROM dual;

A database using AL32UTF8 can produce a result similar to:

Typ=96 Len=3 CharacterSet=AL32UTF8: 41,42,43

Inspect Only Part of the Value

start_position and length restrict the byte portion shown:

SELECT DUMP('ABC', 16, 2, 1)
FROM dual;

This asks Oracle to display one byte beginning at byte position 2.

These arguments operate on the internal byte representation, which matters for multibyte character sets.

Debugging Similar-Looking Strings

DUMP() is particularly useful when two strings look identical but compare differently.

For example, one value might contain:

• a normal space versus a non-breaking space
• a hidden control character
• a different Unicode code point
• trailing bytes that are not visually obvious

Comparing DUMP(value, 1016) outputs can reveal the difference.

NULL

If expr is NULL, DUMP() returns NULL:

SELECT DUMP(NULL)
FROM dual;

Summary

• DUMP() returns a VARCHAR2 describing Oracle’s internal representation of a value.
• It shows the internal type code, byte length, and byte values.
• Use format 16 for hexadecimal output.
• Add 1000 to include character-set information.
• start_position and length let you inspect a subset of the bytes.
• It is useful for datatype and character-encoding troubleshooting.

See Oracle’s DUMP documentation.