CODEWITHSUNDEEP
sqloracle-databaseblob
Roland Tepp
Roland Tepp

Reputation: 8521

How do I get textual contents from BLOB in Oracle SQL

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.

Upvotes: 147

Views: 670856

Answers (12)

phhu
phhu

Reputation: 1992

I can get this to work using TO_CLOB (docs):

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

This works for me in Oracle 19c, with a BLOB field which larger the the VARCHAR limit. I get readable text (from a JSON-holding BLOB)

Upvotes: 17

Swapnil Ingle
Swapnil Ingle

Reputation: 201

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.

Upvotes: 17

Arsen Salamakha
Arsen Salamakha

Reputation: 87

In case your text is compressed inside the blob using DEFLATE algorithm and it's quite large, you can use this function to read it

CREATE OR REPLACE PACKAGE read_gzipped_entity_package AS

FUNCTION read_entity(entity_id IN VARCHAR2)
  RETURN VARCHAR2;

END read_gzipped_entity_package;
/

CREATE OR REPLACE PACKAGE BODY read_gzipped_entity_package IS

FUNCTION read_entity(entity_id IN VARCHAR2) RETURN VARCHAR2
IS
    l_blob              BLOB;
    l_blob_length       NUMBER;
    l_amount            BINARY_INTEGER := 10000; -- must be <= ~32765.
    l_offset            INTEGER := 1;
    l_buffer            RAW(20000);
    l_text_buffer       VARCHAR2(32767);
BEGIN
    -- Get uncompressed BLOB
    SELECT UTL_COMPRESS.LZ_UNCOMPRESS(COMPRESSED_BLOB_COLUMN_NAME)
    INTO   l_blob
    FROM   TABLE_NAME
    WHERE  ID = entity_id;

    -- Figure out how long the BLOB is.
    l_blob_length := DBMS_LOB.GETLENGTH(l_blob);

    -- We'll loop through the BLOB as many times as necessary to
    -- get all its data.
    FOR i IN 1..CEIL(l_blob_length/l_amount) LOOP

        -- Read in the given chunk of the BLOB.
        DBMS_LOB.READ(l_blob
        ,             l_amount
        ,             l_offset
        ,             l_buffer);

        -- The DBMS_LOB.READ procedure dictates that its output be RAW.
        -- This next procedure converts that RAW data to character data.
        l_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(l_buffer);

        -- For the next iteration through the BLOB, bump up your offset
        -- location (i.e., where you start reading from).
        l_offset := l_offset + l_amount;
    END LOOP;
    RETURN l_text_buffer;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('!ERROR: ' || SUBSTR(SQLERRM,1,247));
END;

END read_gzipped_entity_package;
/

Then run select to get text

SELECT read_gzipped_entity_package.read_entity('entity_id') FROM DUAL;

Hope this will help someone.

Upvotes: 3

nullPointer
nullPointer

Reputation: 4574

SQL Developer provides this functionality too :

Double click the results grid cell, and click edit :

enter image description here

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

enter image description here

And that's it!

enter image description here

Upvotes: 40

Narendra Kalekar
Narendra Kalekar

Reputation: 118

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

Upvotes: -2

Reza Rahimi
Reza Rahimi

Reputation: 643

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

Upvotes: 1

Imran Patel
Imran Patel

Reputation: 191

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

SELECT DBMS_LOB.SUBSTR(BLOB_FIELD_NAME) FROM TABLE_NAME;

Upvotes: 19

Sonic Soul
Sonic Soul

Reputation: 24989

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)

enter image description here

Upvotes: 3

Pecos Bill
Pecos Bill

Reputation: 1648

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;

Upvotes: 4

Barn
Barn

Reputation: 954

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;

Upvotes: 8

Mac
Mac

Reputation: 8339

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

Upvotes: 176

Alex
Alex

Reputation: 95

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.

Upvotes: -5

Related Questions