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     lt row id gt      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

User · Answer

Use TO CHAR function   select TO CHAR BLOB FIELD  from TABLE WITH BLOB where ID     lt row id gt     Converts NCHAR  NVARCHAR2  CLOB  or NCLOB data to the database character set  The value returned is always VARCHAR2

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    gt 0

User · Answer

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

User · Answer

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    gt 0

User · Answer

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  lt    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

User · Answer

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     lt row id gt

User · Answer

Use this SQL to get the first 2000 chars of the BLOB   SELECT utl raw cast to varchar2 dbms lob substr  lt YOUR BLOB FIELD gt  2000 1   FROM  lt YOUR TABLE gt     Note  This is because  Oracle will not be able to handle the conversion of BLOB that is more than length 2000

User · Answer

You can use below SQL to read the BLOB Fields from table   SELECT DBMS LOB SUBSTR BLOB FIELD NAME  FROM TABLE NAME

[sql] How do I get textual contents from BLOB in Oracle SQL

Examples related to sql

Examples related to oracle

Examples related to blob