Export BLOB Contents Using UTL_FILE

In a previous article I explained how to export the contents of a BLOB to a file using a Java stored procedure. In Oracle 9iR2 (9.2) it is also possible to perform this operation using new functionality provided by the UTL_FILE package.

First we create a directory object pointing to the destination directory.

CONN / AS SYSDBA CREATE OR REPLACE DIRECTORY BLOBS AS '/tmp/'; GRANT READ, WRITE ON DIRECTORY BLOBS TO my_user;

Next we open the BLOB, read chunks into a buffer and write them to a file.

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 col1 INTO l_blob FROM tab1 WHERE rownum = 1; l_blob_len := DBMS_LOB.getlength(l_blob); -- Open the destination file. --l_file := UTL_FILE.fopen('BLOB_DIR','MyImage.gif','w', 32767); l_file := UTL_FILE.fopen('BLOB_DIR','MyImage.gif','wb', 32767); -- Read chunks of the BLOB and write them to the file -- until complete. WHILE l_pos 
Finally, you can check the file is produced correctly.
Port-specific Bug #2546782, raised against 9.2.0 on Windows 2000, reports wrong output from UTL_FILE.PUT_RAW .
Oracle 10g includes extra open modes (rb, wr, ab) to signify byte mode operation. The "wb" open mode can be used along with the PUT_RAW procedure to prevent extra newline characters being added on a Windows platform.
In reality you would probably put this code into a stored procedure, or packaged procedure. This example is available here.
CREATE OR REPLACE PROCEDURE blob_to_file (p_blob IN BLOB, p_dir IN VARCHAR2, p_filename IN VARCHAR2) AS l_file UTL_FILE.FILE_TYPE; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob_len INTEGER; BEGIN l_blob_len := DBMS_LOB.getlength(p_blob); -- Open the destination file. l_file := UTL_FILE.fopen(p_dir, p_filename,'wb', 32767); -- Read chunks of the BLOB and write them to the file until complete. WHILE l_pos 
You might call this like the following.
DECLARE l_blob BLOB; BEGIN -- Get LOB locator SELECT col1 INTO l_blob FROM tab1 WHERE rownum = 1; blob_to_file(p_blob => l_blob, p_dir => 'BLOB_DIR', p_filename => 'MyImage.gif'); END; /
For more information see:

  Import BLOB Contents
  Export BLOB Contents (8i)
  Import CLOB Contents
  Export CLOB Contents
  DBMS_LOB
  UTL_FILE

Hope this helps. Regards Tim.
Created: 2005-05-14 Updated: 2020-05-22