Cool SQL function: DUMP

I have noticed that, in his latest book, Tom Kyte used the SQL function DUMP quite frequently to expose the internal representation and the size of a specific expression. To be honest, I do not use DUMP very often, but I do find it useful in certain situations.

From the Oracle documentation:

DUMP(expr[, return_fmt
            [, start_position [, length ] ]
         ]
    )

DUMP returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set.

For example:

HR@XE> select dump('hello') from dual;

DUMP('HELLO')
---------------------------------
Typ=96 Len=5: 104,101,108,108,111

The result tells me that the length of ‘hello’ is 5 bytes, its internal representation in decimal is “104,101,108,108,111″, and its type is 96 which means a character datatype. Oracle docs gives a complete list of type codes and descriptions.

Decimal notation is the default. You can format the return value in octal, hexadecimal and as single characters. For example, in octal:

HR@XE> select dump('hello',8) from dual;

DUMP('HELLO',8)
---------------------------------
Typ=96 Len=5: 150,145,154,154,157

in hexadecimal:

HR@XE> c/8/16
  1* select dump('hello',16) from dual
HR@XE> /

DUMP('HELLO',16)
----------------------------
Typ=96 Len=5: 68,65,6c,6c,6f

and as single characters:

HR@XE> c/16/17
  1* select dump('hello',17) from dual
HR@XE> /

DUMP('HELLO',17)
-----------------------
Typ=96 Len=5: h,e,l,l,o

By default, the return value contains no character set information. To retrieve the character set name, add 1000 to any of the preceding format values. For example, a format of 1017 (instead of 17) returns the result in single characters and provides the character set name as well:

HR@XE> c/17/1017
  1* select dump('hello',1017) from dual
HR@XE> /

DUMP('HELLO',1017)
-------------------------------------------------
Typ=96 Len=5 CharacterSet=WE8MSWIN1252: h,e,l,l,o

You can also ask Oracle to return a portion of the internal representation by providing values for start_position and length:

HR@XE> select dump('hello',17,1,3) from dual;

DUMP('HELLO',17,1,3
-------------------
Typ=96 Len=5: h,e,l

Notice that the length is still 5, only the internal representation is “sub-stringed”.

Just curious, have you used the SQL function DUMP in any of your production applications where the logic of the program depended on the result of this function?

Possibly related:

Tagged , | Post a Comment