Export BLOB data from Oracle 10g

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

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob      BLOB;
  l_blob_len  INTEGER;
BEGIN
  -- Get LOB locator
  SELECT file_data
  INTO   l_blob
  FROM   ifsapp.edm_file_storage_tab
  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;
  END LOOP;
 
  -- Close the file.
  UTL_FILE.fclose(l_file);
 
EXCEPTION
  WHEN OTHERS THEN
    -- Close the file if something goes wrong.
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

Any help
DarrenJacksonAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
The code below is untested but should be pretty close.  

If you want to keep processing on an error, just create a nested pl/sql block and move the exception handler inside it.

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_blob      BLOB;
  l_blob_len  INTEGER;
BEGIN
	for i in ( 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) loop
  
		  l_blob_len := DBMS_LOB.getlength(i.file_data);
  
		  -- Open the destination file.
		  l_file := UTL_FILE.fopen('BLOBS',i.title,'w', 32767);

		  -- Read chunks of the BLOB and write them to the file
		  -- until complete.
                  l_pos   := 1;
		  WHILE l_pos < l_blob_len LOOP
		    DBMS_LOB.read(i.file_data, l_amount, l_pos, l_buffer);
		    UTL_FILE.put_raw(l_file, l_buffer, TRUE);
		    l_pos := l_pos + l_amount;
		  END LOOP;
  
		  -- Close the file.
		  UTL_FILE.fclose(l_file);
	end loop;
  
EXCEPTION
  WHEN OTHERS THEN
    -- Close the file if something goes wrong.
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

Open in new window

0
 
DarrenJacksonAuthor Commented:
Thanks for the quick reply ill take a look
0
 
DarrenJacksonAuthor Commented:
Perfect Thankyou
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.