Solved

Export BLOB data from Oracle 10g

Posted on 2016-10-25
4
23 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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Comment Utility
Thanks for the quick reply ill take a look
0
 

Author Closing Comment

by:DarrenJackson
Comment Utility
Perfect Thankyou
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Glad to help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now