How do I get textual contents from BLOB in Oracle

问题:

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.

回答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 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>';

回答2:

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

SELECT DBMS_LOB.SUBSTR(BLOB_FIELD_NAME) FROM TABLE_NAME;

回答3:

If you want to search inside the text, rather than view it, this works:

with unzipped_text as (
  select
    my_id
    ,utl_compress.lz_uncompress(my_compressed_blob) as my_blob
  from my_table
  where my_id='MY_ID'
)
select * from unzipped_text
where dbms_lob.instr(my_blob, utl_raw.cast_to_raw('MY_SEARCH_STRING'))>0;

回答4:

I struggled with this for a while and implemented the PL/SQL solution, but later realized that in Toad you can simply double click on the results grid cell, and it brings up an editor with contents in text. (i'm on Toad v11)

回答5:

Barn's answer worked for me with modification because my column is not compressed. The quick and dirty solution:

select * from my_table
where dbms_lob.instr(my_UNcompressed_blob, utl_raw.cast_to_raw('MY_SEARCH_STRING'))>0;

回答6:

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!

回答7:

You can try this:

SELECT TO_CHAR(dbms_lob.substr(BLOB_FIELD, 3900)) FROM TABLE_WITH_BLOB;

However, It would be limited to 4000 byte

回答8:

Worked for me,

select lcase((insert( insert( insert( insert(hex(BLOB_FIELD),9,0,'-'), 14,0,'-'), 19,0,'-'), 24,0,'-'))) as FIELD_ID from TABLE_WITH_BLOB where ID = 'row id';

回答9:

Use TO_CHAR function.

select TO_CHAR(BLOB_FIELD) from TABLE_WITH_BLOB where ID = '<row id>'

Converts NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set. The value returned is always VARCHAR2.