?
Solved

Export BLOB data from Oracle 10g

Posted on 2016-10-25
4
Medium Priority
?
81 Views
Last Modified: 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

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
Comment
Question by:DarrenJackson
  • 2
  • 2
4 Comments
 
LVL 79

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 41858564
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
 

Author Comment

by:DarrenJackson
ID: 41858570
Thanks for the quick reply ill take a look
0
 

Author Closing Comment

by:DarrenJackson
ID: 41858635
Perfect Thankyou
0
 
LVL 79

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41858643
Glad to help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question