The DUMP Function in Oracle SQL / PLSQL is used to get a VARCHAR2 data type which tells us the data type code, the length of the expression in bytes and the representation of the expression internally.
Syntax for the using the DUMP Function in Oracle SQL / PLSQL is:
SELECT DUMP(expression, [return_format],[start_position],[length])
FROM dual;
- expression is the value to be analyzed.
- return_format is an optional value which determines the format of the return value. It can have the values as shown in the table below:
| Value | Description |
| 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 |
- start_position and length are also optional parameters which determines the portion of the internal representation to show. If omitted, the dump function will display the decimal notation of the entire internal representation.
Examples:
Using DUMP Function in Oracle SQL / PLSQL SELECT statement:
SELECT
DUMP('Tech Honey')
FROM dual;
Will return ‘Typ=96 Len=10: 84,101,99,104,32,72,111,110,101,121’
SELECT
DUMP('The Internet')
FROM dual;
Will return ‘ Typ=96 Len=12: 84,104,101,32,73,110,116,101,114,110,101,116’