• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

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
0
DarrenJackson
Asked:
DarrenJackson
  • 2
  • 2
1 Solution
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now