Oracle – Writing a BLOB to an operating system file

Earlier, I wrote about reading a BLOB from file in an Oracle PL/SQL procedure. Here is an example of writing a BLOB out to file. Just as before, the user will need access to a DIRECTORY object that points to the OS file destination of choice. See the article on loading BLOBs for more information on this.

The write_blob_to_file procedure in the example below provides the functionality to write to file. It has been wrapped in an anonymous block to demonstrate usage:

SQL> DECLARE
  2
  3    temp_blob BLOB;
  4
  5    PROCEDURE write_blob_to_file( filename VARCHAR2, contents BLOB )
  6    AS
  7      begin_index NUMBER DEFAULT 1;
  8      read_size NUMBER;
  9      buffer RAW( 32767 );
 10      outfile UTL_FILE.FILE_TYPE;
 11    BEGIN
 12
 13      -- Open the output file
 14      outfile := UTL_FILE.FOPEN( 'TEST_DIR', filename, 'WB', 32767 );
 15
 16      -- Loop through the BLOB and keep writing in 32767 chunks
 17      WHILE begin_index <= DBMS_LOB.GETLENGTH( contents )
 18      LOOP
 19
 20        read_size := 32767;
 21        DBMS_LOB.READ( contents, read_size, begin_index, buffer );
 22        begin_index := begin_index + read_size;
 23
 24        UTL_FILE.PUT_RAW( outfile, buffer );
 25
 26      END LOOP;
 27
 28      -- Flush and close the output file
 29      UTL_FILE.FFLUSH( outfile );
 30      UTL_FILE.FCLOSE( outfile );
 31
 32    END;
 33
 34  BEGIN
 35
 36    DBMS_LOB.CREATETEMPORARY( temp_blob, FALSE, DBMS_LOB.SESSION );
 37    DBMS_LOB.WRITE( temp_blob, 7, 1, UTL_RAW.CAST_TO_RAW( 'TESTING' ) );
 38
 39    write_blob_to_file( 'testout.dat', temp_blob );
 40
 41  END;
 42  /

PL/SQL procedure successfully completed.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s