CODEWITHSUNDEEP
oracle-databaseselectblob
Angus
Angus

Reputation: 141

read first 1kb of a blob from oracle

I wish to extract just the first 1024 bytes of a stored blob and not the whole file. The reason for this is I want to just extract the metadata from a file as quickly as possible without having to select the whole blob.

I understand the following:

select dbms_lob.substr(file_blob, 16,1) 
from file_upload 
where file_upload_id=504;

which returns it as hex. How may I do this so it returns it in binary data without selecting the whole blob?

Thanks in advance.

Upvotes: 2

Views: 4927

Answers (1)

Gary Myers
Gary Myers

Reputation: 35401

DBMS_LOB.SUBSTR will, for a BLOB, return a RAW. Most environments will render that in hex. You can use the DUMP function to view it in some other formats.

select dump(dbms_lob.substr(product_image,10,1),10), 
       dump(dbms_lob.substr(product_image,10,1),16), 
       dump(dbms_lob.substr(product_image,10,1),17) 
from APEX_DEMO.DEMO_PRODUCT_INFO
where product_id = 9;

This returns the first 10 bytes of the BLOB in decimal (eg 0-255), hex and character. The latter may throw some unprintable garbage to the screen and, if the client and database character sets do not match, undergo some 'translation'.

You can use UTL_RAW.CAST_TO_VARCHAR2 which may give you what you want. 

select utl_raw.cast_to_varchar2(dbms_lob.substr(product_image,10,1)) chr 
from APEX_DEMO.DEMO_PRODUCT_INFO
where product_id = 9

Upvotes: 6

Related Questions