Oracle DUMP Function: A Simple Guide

The DUMP function in Oracle SQL is a powerful diagnostic tool. It's not used for normal reporting but is essential for debugging and internal analysis.

Its purpose is to show you the "under the hood" details of any value: its data type code, its length in bytes, and its actual internal byte-by-byte representation (in decimal, hex, or octal).

What is the DUMP Function in Oracle?

The DUMP(expr, format) function takes any value and returns a VARCHAR2 string describing its internal storage.

This is useful for:

• Seeing the exact byte values of a string (e.g., to find "hidden" characters).
• Understanding the internal difference between VARCHAR2 (Typ=96) and NVARCHAR2 (Typ=1).
• Seeing how a NUMBER is stored.
• Finding out the character set of a string.

DUMP Function Syntax

The syntax for DUMP is:

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

Let's break that down:

• expr: The value or column you want to inspect (e.g., 'abc', salary).
• [return_fmt] (Optional): A number specifying the format for the byte values. The most common are:
  • 10: Decimal (the default).
  • 16: Hexadecimal.
  • 8: Octal.
  • Add 1000 to any format to also show the character set name (e.g., 1016).
• [start_position] (Optional): The byte position to start dumping from.
• [length] (Optional): The number of bytes to dump.

Oracle DUMP Function Examples

Here are two practical examples of how to use DUMP.

Example 1: Inspecting a String with DUMP

This example shows the internal details for the simple string 'abc'. We'll use format 1016 to get the hexadecimal (16) representation plus the character set name (1000).

Query:

SELECT
  DUMP('abc', 1016) AS "Dump_Result"
FROM DUAL;

Result: (This result tells us the string is a VARCHAR2 (Typ=96), is 3 bytes long, and shows the hex values for 'a', 'b', and 'c'.)

Dump_Result
----------------------------------------------
Typ=96 Len=3 CharacterSet=WE8MSWIN1252: 61,62,63

Example 2: Inspecting a Number with DUMP

This example shows that a NUMBER is stored very differently from a string.

Query:

SELECT
  DUMP(100) AS "Number_Dump"
FROM DUAL;

Result: (The result shows the type is NUMBER (Typ=2), it's 2 bytes long, and its internal byte representation is 194,2.)

Number_Dump
---------------------
Typ=2 Len=2: 194,2