You can store text data in BLOB columns, but Oracle does not allow you to compare data in BLOB and CLOB columns without casting. You can use the CAST_TO_VARCHAR2 function from the UTL_RAW package to perform the casting.

Example:

In the example below column_blob is BLOB, while column_clob is CLOB.

SELECT column_blob,column_clob 
FROM tables_lobs
WHERE column_clob = utl_raw.cast_to_varchar2(dbms_lob.substr(column_blob))