It is frequently asked how to load a BLOB table column from the content of an external file.
This can be achieved by using the DBMS_LOB package's functions.
Sometimes it is also useful to extract the BLOB content back to a file.
Here is a procedure that achieves this task:
(Available since Oracle database 9i, that introduced the UTL_FILE.PUT_RAW() function)
CREATE OR REPLACE PROCEDURE Write_Binary_file
(
PC$Directory IN VARCHAR2
,PC$File_Name IN VARCHAR2
,PC$SQL_Order IN VARCHAR2
,PB$Raise IN BOOLEAN DEFAULT FALSE
)
-- -------------------------------------------
-- Procedure to dump a BLOB column onto a file
-- -------------------------------------------
-- parameters:
-- PC$Directory : name of an existing Oracle Directory
-- PC$File_Name : name of the expected output file
-- PC$SQL_Order : SQL order to select the BLOB column
-- PB$Raise : boolean to indicate if the process
-- would be stopped after an error
--
IS
src_lob BLOB;
buffer RAW(16384);
amt BINARY_INTEGER := 16384;
pos INTEGER := 1;
LF$FicOUT UTL_FILE.FILE_TYPE ;
LC$Msg VARCHAR2(2000) ;
BEGIN
-- get the BLOB column --
BEGIN
EXECUTE IMMEDIATE PC$SQL_Order INTO src_lob ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LC$Msg := 'Write_Binary_File(): NO_DATA_FOUND' ;
IF PB$Raise THEN
RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
END IF ;
Dbms_Output.Put_Line(LC$Msg) ;
RETURN ;
END ;
-- open the output file --
LF$FicOUT := UTL_FILE.FOPEN( PC$Directory, PC$File_Name, 'W', 32764 ) ;
-- write the file --
LOOP
-- read the chunks --
Dbms_Lob.READ (src_lob, amt, pos, buffer);
-- write the chunks --
Utl_File.Put_Raw(LF$FicOut, buffer);
pos := pos + amt;
END LOOP;
-- close the file --
Utl_File.Fclose(LF$FicOut);
EXCEPTION
WHEN NO_DATA_FOUND THEN
utl_file.fclose(LF$FicOut);
WHEN OTHERS THEN
LC$Msg := 'Write_Binary_File() Error : ' || TO_CHAR( SQLCODE ) || ' while managing file ('
|| PC$Directory || ') ' || PC$File_Name ;
IF PB$Raise THEN
RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
END IF ;
Dbms_Output.Put_Line(LC$Msg);
END Write_Binary_file;
/
Here is a calling sample:
BEGIN
Write_Binary_file
(
'OUTPUT_DIRECTORY'
,'image.jpg'
,'select image from images where id=3'
);
END;