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: