Sometimes you need to inspect the content of a text blob in Oracle.
Yes, if the content is not a text then this does not make sense, but often times we insert in a blob field a really long text like an XML content or JSON content of a document.
What we need to do is first recover the blob content using the the Oracle DBMS_LOB package. This package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. The package can be used to access and manipulate specific parts of a LOB or complete LOBs.
We will use the function dbms_lob.substr to extract blob data. This function returns amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB. In case of a text blob we just have to indicate as parameter the column name “blobcolumn”.
Second we need to cast the blob content to text. That can be done using another Oracle package utl_raw that contain cast functions of raw data to certain types. We will use the cast_to_varchar2 function in this case.
In the end the whole SQL is just one line:
select utl_raw.cast_to_varchar2(dbms_lob.substr(blobcolumn)) from mytable where ID = '1';The SQL will convert to text a blob value from record with id=1 of column blobcolumn from table mytable.
See as references:
https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/UTL_RAW.html