Oracle DUMP() Function

Oracle DUMP() is a built-in function that returns a VARCHAR2 value containing the data type code, byte length, and internal representation of the expr.

Oracle DUMP() Syntax

Here is the syntax for the Oracle DUMP() function:

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

Parameters

expr

Required.

return_fmt

Optional. Specifies the format of the return value, which can be one of the following values:

  • 8 returns the result in octal representation.
  • 10 returns the result in decimal representation. This is the default value.
  • 16 returns the result in hexadecimal representation.
  • 17 returns the character form of each byte, only if it can be interpreted as a printable character in the compiler’s character set (usually ASCII or EBCDIC). Some ASCII control characters may be printed in the form of ^X. Otherwise, the characters will be represented in hexadecimal. All NLS parameters are ignored. Do not rely on any specific output format of DUMP with return_fmt 17.
start_position

Optional. Specifies the starting position of the substring. Starts from 1.

length

Optional. Specifies the length of the string. If omitted, the substring extends to the end of the string.

The combination of the start_position and length parameters determines which part of the internal representation to return. By default, the entire internal representation is returned in decimal representation.

Return Value

The Oracle DUMP() function returns a VARCHAR2 value containing the data type code, byte length, and internal representation of expr.

The return value of DUMP() always uses the database character set.

By default, the return value does not include character set information. To retrieve the character set name for expr, append 1000 to any preceding format value. For example, return_fmt of 1008 returns the result in octal representation and provides the character set name of expr.

If expr is empty, the function returns NULL.

If any of the parameters are NULL, DUMP() returns NULL.

Oracle DUMP() Examples

Here are some examples that demonstrate the usage of the Oracle DUMP() function.

Basic Usage

This is an example that demonstrates the basic usage of the Oracle DUMP() function:

SELECT
    DUMP('ABC')
FROM dual;

Output:

DUMP('ABC')
_________________________
Typ=96 Len=3: 65,66,67

Return Value Formats

The Oracle DUMP() function allows you to specify the format of the return value. The following statement specifies the format parameter as 8 to return the result in octal representation:

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

Output:

DUMP('ABC',8)
____________________________
Typ=96 Len=3: 101,102,103

The following statement specifies the format parameter as 10 to return the result in decimal representation, which is also the default value for this parameter:

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

Output:

DUMP('ABC',10)            DUMP('ABC')
_________________________ _________________________
Typ=96 Len=3: 65,66,67    Typ=96 Len=3: 65,66,67

The following statement specifies the format parameter as 16 to return the result in hexadecimal representation:

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

Output:

DUMP('ABC',16)
_________________________
Typ=96 Len=3: 41,42,43

Substring

The Oracle DUMP() function allows you to get the internal representation of a substring. This example returns the internal representation of the first letter:

SELECT
    DUMP('ABC', 10, 1, 1),
    DUMP('ABC')
FROM dual;

Output:

DUMP('ABC',10)            DUMP('ABC')
_________________________ _________________________
Typ=96 Len=3: 65,66,67    Typ=96 Len=3: 65,66,67

This example gets the internal representation of the substring made up of the remaining characters starting from the second character:

SELECT
    DUMP('ABC', 10, 2),
    DUMP('ABC')
FROM dual;

Output:

DUMP('ABC',10,2)       DUMP('ABC')
______________________ _________________________
Typ=96 Len=3: 66,67    Typ=96 Len=3: 65,66,67

Character Set

The Oracle DUMP() function allows you to get the character set. To get the character set of the argument, add 1000 to the value of the returned format, such as 1008, 1010, 1016, and 1017.

The following statement returns the character set and decimal representation:

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

Output:

DUMP('ABC',1010)
_______________________________________________
Typ=96 Len=3 CharacterSet=AL32UTF8: 65,66,67

The following statement returns the character set and hexadecimal representation:

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

Output:

DUMP('ABC',1016)
_______________________________________________
Typ=96 Len=3 CharacterSet=AL32UTF8: 41,42,43

NULL Parameters

If any parameter is NULL, DUMP() will return NULL.

SET NULL 'NULL';
SELECT
    DUMP(NULL) NULL_1,
    DUMP(NULL, NULL) NULL_2,
    DUMP(NULL, NULL, NULL) NULL_3,
    DUMP(NULL, NULL, NULL, NULL) NULL_4
FROM dual;

Output:

NULL_1    NULL_2    NULL_3    NULL_4
_________ _________ _________ _________
NULL      NULL      NULL      NULL

In this example, we use the SET NULL 'NULL'; statement to display NULL values as the string 'NULL'.

Conclusion

The Oracle DUMP() function is a built-in function that returns a VARCHAR2 value containing the data type code, byte length, and internal representation of the expr.