2013-12-20

DUMP to HEX


DUMP function gives decimal presentation of a column contents bits.

select ch,dump(ch) dm 
from (
select 'DUMP to DEC' ch 
from dual
)
;


CH DM
DUMP to DEC Typ=96 Len=11: 68,85,77,80,32,116,111,32,68,69,67

Sometimes hexadecimal presentation would be nice

select ch
     , listagg(to_char((column_value).getnumberval(),'XX'))
        within group (order by rownum) hex 
 from (
  select ch,dump(ch) dm 
   from (
    select 'DUMP to HEX' ch 
      from dual
    )
  ) n
  ,xmltable(substr(n.dm,instr(n.dm,':')+2))
;

CH HEX
DUMP to HEX  44 55 4D 50 20 74 6F 20 48 45 58

Or maybe it is easier just to use the documented second parameter of dump function.

select ch,dump(ch,1016) dm 
 from (
  select 'DUMP to HEX' ch 
  from dual
 )
;


DUMP to HEX    Typ=96 Len=11 CharacterSet=AL32UTF8: 44,55,4d,50,20,74,6f,20,48,45,58

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.