read first 1kb of a blob from oracle

Question

Hi,

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.

Answer 1

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