How do I get textual contents from BLOB in Oracle SQL

Question

I am trying to see from an SQL console what is inside an Oracle BLOB.

I know it contains a somewhat large body of text and I want to just see the text, but the following query only indicates that there is a BLOB in that field:

select BLOB_FIELD from TABLE_WITH_BLOB where ID = '<row id>';

the result I'm getting is not quite what I expected:

    BLOB_FIELD
    -----------------------
    oracle.sql.BLOB@1c4ada9

So what kind of magic incantations can I do to turn the BLOB into it's textual representation?

PS: I am just trying to look at the content of the BLOB from an SQL console (Eclipse Data Tools), not use it in code.

Answer 1

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 32167 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>';

Answer 2

"a somewhat large body of text" That's a bit vague. Do you mean just words, or is it text in a structured document (eg word, pdf, html). If the latter look into Oracle Text

Answer 3

If you know your data will be text, you can store it as a CLOB.