DUMP Function in Oracle SQL – PLSQL

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’


Tagged , , , , , , , , , , , . Bookmark the permalink.