How to Load File Content to a BLOB Field and Unload BLOB Content to a File on the OS

How to Load File Content to a BLOB Field and Unload BLOB Content to a File on the OS

How to Load File Content to a BLOB Field and Unload BLOB Content to a File on the OS

Titleimage

Posted by Patrick Hamou on 2016:04:19 19:28:05

Applies to

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3
Information in this document applies to any platform.

Goal

This note describes steps to Load a Binary file into a BLOB Column of a table, and also describes how to unload contents of a blob column to a file on the filesystem.

 

RENAPS Offers IT Managed Services. Learn More about how we can help your business

Solution

Load contents of Binary File to a blob column of a table

Create these 2 tables

SQL> CREATE TABLE MYLOB ( ID NUMBER, PHOTO BLOB ) ; 
SQL> CREATE TABLE TEMP_BFILE( B_FILE BFILE) ; 

Insert bfile locator for the binary file into temp_file 

SQL> insert into temp_bfile values ( bfilename('MYDIR','pic1.jpg')); 

MYDIR is a directory object created with the CREATE DIRECTORY command.

Inserting lob into file 

SQL> declare 
tmp_blob blob default EMPTY_BLOB(); 
tmp_bfile bfile:=null; 
dest_offset integer:=1; 
src_offset integer:=1; 
begin 
select b_file into tmp_bfile from temp_bfile; 
DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY); 
dbms_lob.createtemporary(tmp_blob, TRUE); 
DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset); 
insert into mylob values(1,tmp_blob); 
DBMS_LOB.CLOSE(tmp_bfile); 
commit; 
end; 

Unload contents of a blob column of a table to a file on the filesystem

Offload the blob to a file 

SQL> declare 
tmp_blob blob default empty_blob(); 
begin 
dbms_lob.createtemporary(tmp_blob, true); 
select photo into tmp_blob from mylob; 
sys.retrieve_lob_to_file (tmp_blob, 'MYDIR','mypic1.jpg'); 
end; 
/ 

Note : MYDIR is a directory object created with the CREATE DIRECTORY command

Source for procedure retrieve_lob_to_file

CREATE OR REPLACE PROCEDURE retrieve_lob_to_file(temp_blob in BLOB, file_path in varchar2, file_name in varchar2) IS 
data_buffer RAW (32767); 
position INTEGER := 1; 
filehandle utl_file.file_type; 
error_number NUMBER; 
error_message VARCHAR2(100); 
blob_length INTEGER; 
chunk_size BINARY_INTEGER := 32767; 
BEGIN 
blob_length := dbms_lob.getlength(temp_blob); 

filehandle := utl_file.fopen(file_path, file_name,'wb', 1024); 

WHILE position < blob_length LOOP 

dbms_lob.read (temp_blob, chunk_size, position, data_buffer); 

utl_file.put_raw (filehandle, data_buffer); 
position := position + chunk_size; 
data_buffer := null; 
END LOOP; 

utl_file.fclose(filehandle); 


EXCEPTION 
WHEN OTHERS THEN 
BEGIN 
error_number := sqlcode; 
error_message := substr(sqlerrm ,1 ,100); 
dbms_output.put_line('Error #: ' || error_number); 
dbms_output.put_line('Error Message: ' || error_message); 
utl_file.fclose_all; 
END; 
END; 
/ 

Please note the plsql code in the above examples is provided for learning purposes only however it has been tested  on a 10.2.0.1 database, and thorough testing is necessary before implementing this on production.

Posted by Patrick Hamou on 2016:04:19 19:28:05

Return to Blog