Playing around with Blob data stored in Oracle

Below are some tips on how to read (peek) at the content of the BLOB.

1) First, to find out the LENGTH of the BLOB

SQL> SELECT length(USER_XDATA2)
FROM TRXNLOG20110317
WHERE TRXN_LOG_ID = 21;

LENGTH(USER_XDATA2)
----------------------
10

1 rows selected

Then, to peek on the content of the BLOB.

SQL> SELECT dbms_lob.substr(USER_XDATA2,8,1)
FROM TRXNLOG20110317
WHERE TRXN_LOG_ID = 21;

DBMS_LOB.SUBSTR(USER_XDATA2,8,1)
-------------------------------------------------------------------------------------------------
0016FEFFFFFFFFFF (Hex value representation)

1 rows selected