PL/SQL : a procedure to output content of BLOB columns onto the file system

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;

Comments

[zian]

Merci pour ce code.

[Laurent Schneider]

pas vraiment en rapport, mais dans Oracle 11g,  on peut sélectionner un BLOB depuis SQLPLUSSQL> create table t(x blob);Table created.SQL> insert into t values ('ABCD');1 row created.SQL> select * from t;X----ABCD