How to Get Textual Contents from Blob in Oracle SQL

How do I get textual contents from BLOB in Oracle SQL

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

How to read BLOB from oracle db?

Run the query, just query the BLOB column.

Double click on the cell.

Check the 'text' value.

Voila.

oracle blob text search

This is quite possible and easy to do.

Simply use dbms_lob.instr in conjunction with utl_raw.cast_to_raw

So in your case, if t1 is a BLOB the select would look like:

select *
  from table1
 where dbms_lob.instr (t1, -- the blob
                   utl_raw.cast_to_raw ('foo'), -- the search string cast to raw
                   1, -- where to start. i.e. offset
                   1 -- Which occurrance i.e. 1=first
                    ) > 0 -- location of occurrence. Here I don't care.  Just find any
;

extract BLOB from Oracle

Create a directory the database can use to export the data:

CREATE DIRECTORY export_dir AS '/path/to/your/directory';

Then use a PL/SQL script to export all the BLOBs to that directory:

DECLARE
  v_start          NUMBER(38,0);
  v_size  CONSTANT NUMBER( 5,0) := 32000;
  v_len            NUMBER(38,0);
  v_buffer         RAW(32000);
  v_file           UTL_FILE.FILE_TYPE;
BEGIN
  FOR r IN ( SELECT filename, bbl_fic FROM your_table )
  LOOP
    v_file  := UTL_FILE.FOPEN('EXPORT_DIR', r.filename, 'wb', 32760 );
    v_start := 1;
    v_len   := DBMS_LOB.GETLENGTH( r.bbl_fic );
    WHILE v_start <= v_len LOOP
      DBMS_LOB.READ(
        r.bbl_fic,
        LEAST( v_len - v_start + 1, v_size ),
        v_start,
        v_buffer
      );

      UTL_FILE.PUT_RAW( v_file, v_buffer );
      UTL_FILE.FFLUSH( v_file ); 
      v_start := v_start + v_size;
    END LOOP;
    UTL_FILE.FCLOSE( v_file );
  END LOOP;
END;
/

extract value present between XML tags from a blob in Oracle SQL

I would not recommend searching for XML tags with INSTR as it is space sensitive, for instance, it wouldn't find <idBiller>111</idBiller > or <idBiller a="x">111</idBiller>. It is XML after all, which is supposedly easy to parse. (Nobody mentioned the awfull syntax, though).

Secondly, BLOB is totally the wrong datatype. It is for binary data. Just wait for the first customer called Agüero or Jørgensen. The appropriate datatype for XML in Oracle is XMLTYPE:

CREATE TABLE admintxnunauthdata (
  accountno NUMBER,
  msg XMLTYPE
) XMLTYPE COLUMN msg STORE AS SECUREFILE BINARY XML (COMPRESS HIGH);

INSERT INTO admintxnunauthdata (accountno, msg) VALUES (1,
 '<PayBillerRequestDTO><idCustomer>00000024</idCustomer><idBiller>VODA</idBiller><billerName>ojas yadnik </billerName><billReferenceNumber>111</billReferenceNumber></PayBillerRequestDTO>');
INSERT INTO admintxnunauthdata (accountno, msg) VALUES (2,
 '<PayBillerRequestDTO><idCustomer>00000025</idCustomer><idBiller>JODA</idBiller><billerName>ojas yadnik </billerName><billReferenceNumber>222</billReferenceNumber></PayBillerRequestDTO>');

You can then search records by idBiller:

SELECT * 
  FROM admintxnunauthdata
 WHERE XMLExists('/PayBillerRequestDTO[idBiller="VODA"]' PASSING msg);

and extract tag values from the XML:

SELECT accountno,
       XMLQuery('/PayBillerRequestDTO/billReferenceNumber/text()' 
         PASSING msg RETURNING CONTENT)
  FROM admintxnunauthdata
 WHERE XMLExists('/PayBillerRequestDTO[idBiller="VODA"]' PASSING msg);

To convert the XML text to a normal datatype, use

SELECT accountno,
       XMLCast(XMLQuery('/PayBillerRequestDTO/billReferenceNumber/text()' 
         PASSING msg RETURNING CONTENT) AS NUMBER) AS billreferencenumber
  FROM admintxnunauthdata
 WHERE XMLExists('/PayBillerRequestDTO[idBiller="VODA"]' PASSING msg);

ACCOUNTNO BILLREFERENCENUMBER
        1                 111

If you cannot change (or let change) the table structure, you could in theory convert the BLOB to XMLTYPE on the fly, but I have no idea about the performance impact:

SELECT accountno,
       XMLCast(XMLQuery('/PayBillerRequestDTO/billReferenceNumber/text()' 
         PASSING msg RETURNING CONTENT) AS NUMBER) AS billreferencenumber
  FROM (
        SELECT accountno, XMLTYPE(msg,1) as msg 
          FROM admintxnunauthdata
       )
 WHERE XMLExists('/PayBillerRequestDTO[idBiller="VODA"]' PASSING msg);

For the conversion from BLOB to XMLTYPE, see convert oracle blob to xml type

---

Related Topics