DUMP is one of the vital string/char functions of Oracle. It is used to return a VARCHAR2 value for an expression. The value returned specifies the datatype code, along with the length of the expression (in bytes), and the internal representation of the passed expression. The DUMP function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i.
Syntax:
DUMP( expression, return_format, start_position, length )
Parameters:
expression: It is used to specify the expression to analyze.
return_format: It is an optional parameter which is used to determine the format of the return value.
start_position: It is also an optional parameter which is used to specify the start position in the internal representation to return.
length:It is also an optional parameter which is used to specify the length of the internal representation to return.
Example 1:
DUMP ( ‘HELLO’ ) |
Output:
'Typ=96 Len=4: 72,69,76,76,79'
Explanation:
The type 96 represents the character set and the default return format is the decimal notation and so is the result.
Example 2:
DUMP ( ‘HELLO’, 8 ) |
Output:
'Typ=96 Len=4: 110,105,114,114,117'
Explanation:
The type 96 represents the character set and the return format 8 represents the octal notation and so is the result.
Example 3:
DUMP ( ‘HELLO’, 10 ) |
Output:
'Typ=96 Len=4: 72,69,76,76,79'
Explanation:
The type 96 represents the character set and the return format 10 represents the decimal notation and so is the result.
Example 4:
DUMP ( ‘HELLO’, 16 ) |
Output:
'Typ=96 Len=4: 48,45,4C,4C,4F’
Explanation:
The type 96 represents the character set and the return format 16 represents the hexadecimal notation and so is the result.
Example 5:
DUMP ( ‘HELLO’, 1016 ) |
Output:
'Typ=96 Len=4 CharacterSet=US7ASCII: 48,45,4C,4C,4F’
Explanation:
The type 96 represents the character set and the return format 1016 represents the hexadecimal notation with the character set name and so is the result.