How to select blob from table

Many times, we may need to check the data stored in the blob field in the table. If you just do select, it will not give you proper output. Here are some of the ways on How to select blob from table

Using sqlplus

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

SELECT DBMS_LOB.SUBSTR(BLOB_FIELD_NAME) FROM <table name>;

It will read the first 2000 chars in the blob
SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(<YOUR_BLOB_FIELD>,2000,1)) FROM <YOUR_TABLE>;

This converts data to the database character set. it returned the value in VARCHAR2.This will be limited to 4000 Bytes

select TO_CHAR(BLOB_FIELD) from <table name> where ID = '<row id>';

SELECT TO_CHAR(dbms_lob.substr(BLOB_FIELD, 3900)) FROM <table name>;

Using Toad or SQL developer

In Toad or SQL developer, if you select the blob column, it will show a blob in the text. And then you can double click to open in a text editor to view it

How to read LOB data using plsql

set serveroutput on size 1000000;
declare
  my_lob BLOB;
  Buffer      RAW(255);
  Amount      BINARY_INTEGER := 255;
  Position    INTEGER := 1;
begin
   select <blob column> into my_lob 
   from <table name>
   where id = &enter_id;
 
   dbms_lob.open(my_lob, dbms_lob.lob_readonly);
   DBMS_OUTPUT.PUT_LINE('Start of data');

   loop
      DBMS_LOB.READ(my_lob, Amount, Position, Buffer);
      /* Process the buffer: */
      DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer));
      Position := Position + Amount;
   end loop;

   dbms_lob.close(my_lob);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('End of data');
end;
/

I hope you like this content. Please do provide the feedback

Also Reads
How to find space usage of lob segments in Oracle database
how to move lob segment from one tablespace to another