Reading and Writing MS-Word RTF document (or any blob) with Oracle PLSQL

I am currently working on a consultancy job (through my company Rhea Solutions Limited) with a client who needed some help with Oracle Apex & PLSQL. One of the things they wanted help with was being able to read and write to a MS-Word document.  The requirement was pretty simple.  They had an RTF template document which they used to send a particular type of communication to their customers.  Some of the content in the file needed to be updated from a database table and the final output RTF document sent off to their customers.  The customer could review it and make changes if required and send it back.  They wanted to stick with Ms-Word, so it would be easier for their customers.

The main issue they had was finding the best way to read the template file, update it and then write it back out as an RTF document. And this is one of the things I was helping them with and I thought I’d share the PLSQL code in this post.

For the sake of this post, I am going to ignore the update part of the requirement, since I think this is relatively straightforward.  I will instead show you a way to read up a word document and write it out.   My PLSQL code to do this was pretty straight-forward. 

 I started off by reading the template file into a temporary BLOB object:

DBMS_LOB.CREATETEMPORARY(l_input_file, TRUE, DBMS_LOB.CALL);  --create my temporary blob
DBMS_LOB.OPEN(l_template_file, DBMS_LOB.LOB_READONLY);  --open template in read mode
DBMS_LOB.OPEN(l_input_file, DBMS_LOB.LOB_READWRITE);   --open blob in read-write mode

--Read from the template file into the local blob variable
DBMS_LOB.LOADFROMFILE( dest_lob => l_input_file, src_lob => l_template_file,

                       amount => DBMS_LOB.GETLENGTH(l_template_file));
DBMS_LOB.CLOSE(l_template_file);

To do the update, the best way I found was to convert the BLOB to CLOB and then just call REPLACE on it. 

Now to the interesting bit of the code – the write to the final output file.  We are going to use the utl_file and the put_raw method in it to write out chunks of the BLOB.  Put_raw has a limit of 32767, so that’s what we’ll check for and if the BLOB is bigger we’ll loop through till we write out the entire BLOB.  And here’s the code to do it.

l_chunksize := DBMS_LOB.GETCHUNKSIZE(l_input_file);  --get the size of the lob
IF (l_chunksize < 32767) THEN   --set the correct size for reading from the blob
     l_buffer_size := l_chunksize;
ELSE
    l_buffer_size := 32767;
END IF;
l_byte_len := l_buffer_size;
l_final_file := utl_file.fopen('TEMPLATE_DIR', p_dest, 'wb', 32767);
l_dest_offset := 1;

WHILE l_byte_len >= l_buffer_size
LOOP
     DBMS_LOB.READ(lob_loc => l_input_file, amount => l_byte_len, offset => l_dest_offset, buffer => l_raw);
     l_dest_offset := l_dest_offset + l_byte_len;
     UTL_FILE.PUT_RAW (file => l_final_file, buffer => l_raw, autoflush => true);
     UTL_FILE.FFLUSH(file => l_final_file);
END LOOP;

utl_file.fflush(file => l_final_file);
utl_file.fclose(l_final_file);

I have written the above as a procedure which takes in a source file name and a destination file name and reads/writes the document. You can of course expand on this as required. 

Here’s the complete procedure code. Please do test thoroughly before you use it in your own applications though!

CREATE OR REPLACE PROCEDURE p_create_output_file(p_source VARCHAR2, p_dest VARCHAR2)
AS
   l_input_file BLOB;
   l_template_file BFILE := BFILENAME('TEMPLATE_DIR', p_source);
   l_final_file utl_file.file_type;
   l_byte_len NUMBER ;
   l_raw RAW(32767);
   l_chunksize INTEGER;
   l_buffer_size BINARY_INTEGER;
BEGIN
   --Create the two temporary lobs
   DBMS_LOB.CREATETEMPORARY(l_input_file, TRUE, DBMS_LOB.CALL);

  --Read from the template file into the local variable blob
   DBMS_LOB.OPEN(l_template_file, DBMS_LOB.LOB_READONLY);
   DBMS_LOB.OPEN(l_input_file, DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.LOADFROMFILE( dest_lob => l_input_file,  src_lob => l_template_file, amount => DBMS_LOB.GETLENGTH(l_template_file));

   DBMS_LOB.CLOSE(l_template_file);
  --Code to update the contents of the file here----

  --Now write the BLOB into the output file
   l_chunksize := DBMS_LOB.GETCHUNKSIZE(l_input_file);
   IF (l_chunksize < 32767) THEN
      l_buffer_size := l_chunksize;
   ELSE
      l_buffer_size := 32767;
   END IF;

   l_byte_len := l_buffer_size;
   l_final_file := utl_file.fopen('TEMPLATE_DIR', p_dest, 'wb', 32767);
   l_dest_offset := 1;

   WHILE l_byte_len >= l_buffer_size
   LOOP
      DBMS_LOB.READ(lob_loc => l_input_file, amount => l_byte_len, offset => l_dest_offset, buffer => l_raw);
      l_dest_offset := l_dest_offset + l_byte_len;
      UTL_FILE.PUT_RAW (file => l_final_file, buffer => l_raw, autoflush => true);

      UTL_FILE.FFLUSH(file => l_final_file);
   END LOOP;
   utl_file.fflush(file => l_final_file);

   --Close open files and release the temporary lobs
   utl_file.fclose(l_final_file);
   DBMS_LOB.FREETEMPORARY(l_input_file);
END;
/

Hopefully you will find this post useful. And if you do, do drop me a comment.  Also, do let me know if there’s anything else you’d like to see me blog on. Thanks