Read first 1kb blob from oracle
I only want to retrieve the first 1024 bytes of the saved blob, not the whole file. The reason for this is that I want to extract the metadata from the file as quickly as possible without having to select the entire 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 can I do this so that it returns it in binary data without highlighting the whole blob?
Thanks in advance.
a source to share
DBMS_LOB.SUBSTR, for BLOB, will return RAW. Most environments will display this in hexadecimal format. 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 (e.g. 0-255), hexadecimal and character. The latter can throw some non-printable garbage on the screen and, if the client and database character sets do not match, you will undergo some "translation".
You can use UTL_RAW.CAST_TO_VARCHAR2 which can 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
a source to share