How do I get textual contents from BLOB in Oracle SQL

Use this SQL to get the first 2000 chars of the BLOB.

SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(<YOUR_BLOB_FIELD>,2000,1)) FROM <YOUR_TABLE>;

Note: This is because, Oracle will not be able to handle the conversion of BLOB that is more than length 2000.

You can use below SQL to read the BLOB Fields from table.

SELECT DBMS_LOB.SUBSTR(BLOB_FIELD_NAME) FROM TABLE_NAME;

First of all, you may want to store text in CLOB/NCLOB columns instead of BLOB, which is designed for binary data (your query would work with a CLOB, by the way).

The following query will let you see the first 32767 characters (at most) of the text inside the blob, provided all the character sets are compatible (original CS of the text stored in the BLOB, CS of the database used for VARCHAR2) :

select utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD)) from TABLE_WITH_BLOB where ID = '<row id>';

SQL Developer provides this functionality too :

Double click the results grid cell, and click edit :

Then on top-right part of the pop up , "View As Text" (You can even see images..)

And that's it!