PLSQL | DUMP Function

The PLSQL DUMP function is used to return a varchar2 value that contains the datatype code, the length in bytes, and the internal representation of the expression.
The PLSQL DUMP function accepts an expression as a parameter, if the expression value is NULL, then the DUMP function returns NULL.

Syntax:

DUMP( expression [, return_format] [, start_position] [, length] )

Parameters Used:

1. expression –
   It is used to specify the expression to be analysed.
2. return_format –
   It is an optional parameter which determines the format of the return value.
3. start-position –
   It is an optional parameter which is used to specify the start position in the internal representation to return.
4. length –
   It is an optional parameter which is used to specify the length in the internal representation to return.

The return_format parameter accepts the following values:

• 8 : octal notation
• 10 : decimal notation
• 16 : hexadecimal notation
• 17 : single characters
• 1008 : octal notation with the character set name
• 1010 : decimal notation with the character set name
• 1016 : hexadecimal notation with the character set name
• 1017 : single characters with the character set name

Return Value:
The DUMP function returns a VARCHAR2 value but if the return_format, start_position and length parameters are omitted, the DUMP function will return the entire internal representation in decimal notation.

Supported Versions of Oracle/PLSQL:

1. Oracle 12c
2. Oracle 11g
3. Oracle 10g
4. Oracle 9i
5. Oracle 8i

Example-1:

SELECT DUMP('Geeksforgeeks') FROM dual 

Output:

Example-2:

SELECT DUMP('Geeksforgeeks', 10) FROM dual 

Output:

Example-3:

SELECT DUMP('Geeksforgeeks', 16) FROM dual 

Output:

Example-4:

SELECT DUMP('Geeksforgeeks', 17) FROM dual 

Output:

Example-5:

SELECT DUMP('Geeksforgeeks', 1008) FROM dual 

Output: