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