Re: HEX dump of BLOB field

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 29 Jun 2001 18:34:01 -0700
Message-ID: <9hjaa90t3s@drn.newsguy.com>

In article <9hj4ul$46a$1_at_slb3.atl.mindspring.net>, "wkb" says...
>
>Hi -
>Anyone know how to get a Hex-dump of a BLOB field in Oracle 8i PL/SQL?
>(Would like to use this in SQL+).
>
>I'm aware of a RAWTOHEX() function that will do this for the RAW type;
>unsure why this does not exist for BLOB - surely there is a developer tool
>to do this.
>
>Thanks for any info -
>Kent
>
>

just use dbms_lob.substr and pick off any 2000 byte or less (4000 characters is a SQL limit, 2000 bytes = 4000 characters of hex) substr you want:

ops$tkyte_at_ORA8I.WORLD> create table t ( x blob );

Table created.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> declare
  2 l_data raw(255);
  3 begin

  4          for i in 1 .. 255
  5          loop
  6                  l_data := l_data || hextoraw( to_char(i,'fmxx') );
  7          end loop;
  8  
  9          insert into t values ( l_data );

 10 end;
 11 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> column x format a80
ops$tkyte_at_ORA8I.WORLD> select dbms_lob.substr( x, 25, 1 ) x from t;

X

---

0102030405060708090A0B0C0D0E0F10111213141516171819

If you want to see the actual "blob" you can use utl_raw:

ops$tkyte_at_ORA8I.WORLD> select utl_raw.cast_to_varchar2( dbms_lob.substr( x, 25, 32 ) ) x from t;

X

---

 !"#$%&'()*+,-./012345678

if utl_raw is not installed in your database, simply:

o cd $ORACLE_HOME/rdbms/admin
o log into svrmgrl using SYS or internal o @utlraw
o @prvtrawb.plb

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri Jun 29 2001 - 20:34:01 CDT