Export BLOB data from Oracle 10g
Posted on 2016-10-25
Guys I am working on a project to extract large amounts of BLOBS data back into their original file types, I have it where I can extract 1 BLOB but I'm looking at thousands here so any help is greatly appreciated.
The Title is taken from this sql along with the BLOB
select IFSAPP.DOC_CLASS_API.Get_Name(b.DOC_CLASS)name,IFSAPP.DOC_TITLE_API.Get_Title(b.DOC_CLASS, b.DOC_NO) as title,b.doc_no,b.doc_class,b.file_data from ifsapp.edm_file_storage_tab b
Here is the code I have found so far if this can be modified then great. My goal is to extract the BLOBS and have then named as they where originally created using the TITLE from the above sql script. I will group the data by using the DOC_CLASS to download in batches
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
-- Get LOB locator
WHERE doc_no = '1179741';
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen('BLOBS','MyImage.pdf','w', 32767);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
-- Close the file.
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN