DBA Tips Archive for Oracle


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC



The following example is part of the Oracle LOB Examples Collection.

This example provides two PL/SQL procedures that demonstrate how to populate a BLOB column with a binary file (a graphics file) then write it back to the file system as a different file name.

Load BLOB from File Image

This PL/SQL procedure loads the graphics file on disk to a BLOB column using a BFILE reference variable.

Write BLOB to File

This PL/SQL procedure writes the contents of the BLOB column in the database piecewise back to the file system.


It should be noted that in Oracle9i this PL/SQL procedure does not work with all binary files. This is due to Oracle bug (BUG#: 2883782). The PL/SQL procedure that I use to write binary (raw) data out is UTL_FILE.PUT_RAW. This procedure, along with UTL_FILE.GET_RAW, was introduced in Oracle 9i Release 2 as previous versions of UTL_FILE only worked with TEXT files.

In Oracle9i there is currently a restriction of a maximum of 32k that can be written with PUT_RAW unless you insert new line characters in between the data. In Oracle10g there is a new binary mode. When files are opened with this mode, ("wb"), any amount of raw data can be written without the need for new lines. In short, this is a bug that can bite you if your binary files do not have a new line character within the RAW data in your MAX_LINESIZE buffer. If you do get bit by this bug in Oracle9i, there is no solution that I have found other than writing it in Java.

When I originally started writing and testing the procedure, I was using a fairly small image file (~ 1KB). This worked fine. I later tested a large PDF file (~ 3MB) and it failed (after writing only a small portion of the file) with the following exception:

BEGIN Write_BLOB_To_File; END;
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 1007
ORA-06512: at "SCOTT.WRITE_BLOB_TO_FILE", line 74
ORA-06512: at line 1

Again, using the new binary write mode in Oracle10g when opening binary files should fix this.


Example binary (image) file to load into BLOB column.


Create an example Oracle directory and table to store a binary file (an image file) into the BLOB column.

DROP TABLE test_blob CASCADE CONSTRAINTS / Table dropped. CREATE TABLE test_blob ( id NUMBER(15) , file_name VARCHAR2(1000) , image BLOB , timestamp DATE ) / Table created. CREATE OR REPLACE DIRECTORY EXAMPLE_LOB_DIR AS '/u04/app/oracle/lobs' / Directory created.

Now, let's define two example PL/SQL procedures.


CREATE OR REPLACE PROCEDURE Load_BLOB_From_File_Image AS dest_loc BLOB; src_loc BFILE := BFILENAME('EXAMPLE_LOB_DIR', 'iDevelopment_info_logo_2.tif'); BEGIN -- +-------------------------------------------------------------+ -- | INSERT INITIAL BLOB VALUE (an image file) INTO THE TABLE | -- +-------------------------------------------------------------+ INSERT INTO test_blob (id, file_name, image, timestamp) VALUES (1001, 'iDevelopment_info_logo_2.tif', empty_blob(), sysdate) RETURNING image INTO dest_loc; -- +-------------------------------------------------------------+ -- | OPENING THE SOURCE BFILE IS MANDATORY | -- +-------------------------------------------------------------+ DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY); -- +-------------------------------------------------------------+ -- | OPENING THE LOB IS OPTIONAL | -- +-------------------------------------------------------------+ DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE); -- +-------------------------------------------------------------+ -- | SIMPLY CALL "loadfromfile" TO LOAD FILES INTO A LOB COLUMN | -- +-------------------------------------------------------------+ DBMS_LOB.LOADFROMFILE( dest_lob => dest_loc , src_lob => src_loc , amount => DBMS_LOB.getLength(src_loc)); -- +-------------------------------------------------------------+ -- | CLOSING ANY LOB IS MANDATORY IF YOU HAVE OPENED IT | -- +-------------------------------------------------------------+ DBMS_LOB.CLOSE(dest_loc); DBMS_LOB.CLOSE(src_loc); COMMIT; END; /

SQL> @load_blob_from_file_image.sql Procedure created.


CREATE OR REPLACE PROCEDURE Write_BLOB_To_File AS v_lob_loc BLOB; v_buffer RAW(32767); v_buffer_size BINARY_INTEGER; v_amount BINARY_INTEGER; v_offset NUMBER(38) := 1; v_chunksize INTEGER; v_out_file UTL_FILE.FILE_TYPE; BEGIN -- +-------------------------------------------------------------+ -- | SELECT THE LOB LOCATOR | -- +-------------------------------------------------------------+ SELECT image INTO v_lob_loc FROM test_blob WHERE id = 1001; -- +-------------------------------------------------------------+ -- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN | -- +-------------------------------------------------------------+ v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc); IF (v_chunksize < 32767) THEN v_buffer_size := v_chunksize; ELSE v_buffer_size := 32767; END IF; v_amount := v_buffer_size; -- +-------------------------------------------------------------+ -- | OPENING THE LOB IS OPTIONAL | -- +-------------------------------------------------------------+ DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY); -- +-------------------------------------------------------------+ -- | WRITE CONTENTS OF THE LOB TO A FILE | -- +-------------------------------------------------------------+ v_out_file := UTL_FILE.FOPEN( location => 'EXAMPLE_LOB_DIR', filename => 'iDevelopment_info_logo_2_NEW.tif', open_mode => 'wb', max_linesize => 32767); WHILE v_amount >= v_buffer_size LOOP DBMS_LOB.READ( lob_loc => v_lob_loc, amount => v_amount, offset => v_offset, buffer => v_buffer); v_offset := v_offset + v_amount; UTL_FILE.PUT_RAW ( file => v_out_file, buffer => v_buffer, autoflush => true); UTL_FILE.FFLUSH(file => v_out_file); -- +-------------------------------------------------------------+ -- | HEY WAIT, THIS IS A BINARY FILE! WHAT IS THIS NEW_LINE | -- | PROCEDURE DOING HERE? THIS WAS A TEST I WAS PERFORMING TO | -- | CONFIRM A BUG (bug#: 2883782). IN 9i THERE IS CURRENTLY A | -- | RESTRICTION OF A MAXIMUM OF 32K THAT CAN BE WRITTEN WITH | -- | PUT_RAW UNLESS YOU INSERT NEW LINE CHARACTERS IN BETWEEN | -- | THE DATA. IN 10i THERE IS A NEW BINARY MODE. WHEN FILES ARE | -- | OPENED WITH THIS MODE ANY AMOUNT OF RAW DATA CAN BE WRITTEN | -- | WITHOUT THE NEED FOR NEW LINES. IN SHORT, THIS IS A BUG | -- | THAT, IF IT CREEPS UP IN ORACLE9i, THERE IS NO SOLUTION! | -- +-------------------------------------------------------------+ -- UTL_FILE.NEW_LINE(file => v_out_file); END LOOP; UTL_FILE.FFLUSH(file => v_out_file); UTL_FILE.FCLOSE(v_out_file); -- +-------------------------------------------------------------+ -- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT | -- +-------------------------------------------------------------+ DBMS_LOB.CLOSE(v_lob_loc); END; /

SQL> @write_blob_to_file.sql Procedure created.


[oracle@racnode1 lobs]$ ls -l /u04/app/oracle/lobs/iDevelopment_info_logo_2.tif -rw-rw-r-- 1 oracle dba 16706 Mar 14 20:29 /u04/app/oracle/lobs/iDevelopment_info_logo_2.tif [oracle@racnode1 lobs]$ sqlplus scott/tiger SQL> EXEC Load_BLOB_From_File_Image PL/SQL procedure successfully completed. SQL> EXEC Write_BLOB_To_File PL/SQL procedure successfully completed. SQL> SELECT id, file_name, DBMS_LOB.GETLENGTH(image) Length, timestamp FROM test_blob WHERE id = 1001; ID FILE_NAME LENGTH TIMESTAMP ---------- ----------------------------------- ---------- -------------------- 1001 iDevelopment_info_logo_2.tif 16706 15-MAR-2012 09:44:00 SQL> exit [oracle@racnode1 lobs]$ ls -l /u04/app/oracle/lobs/iDevelopment_info_logo_2* -rw-r--r-- 1 oracle asmadmin 16706 Mar 15 09:44 /u04/app/oracle/lobs/iDevelopment_info_logo_2_NEW.tif -rw-rw-r-- 1 oracle dba 16706 Mar 14 20:29 /u04/app/oracle/lobs/iDevelopment_info_logo_2.tif


If you notice that the newly created file(s) are 1 byte larger than the original, then read my article entitled CR/LF Added to Binary File When Using UTL_FILE.PUT_RAW for an explanation on why this happens and for the workaround.

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.

Copyright (c) 1998-2020 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 15-Mar-2012 09:46:41 EDT
Page Count: 115039