Blog 12/07/2021

Integrating PL/SQL table records into Word

Michel Vogelpoel /

Integratie expert

In a previous blog I talked about replacing text tags in a Word docx template. So replacing single text item tags in the document with a value from the database (for instance the tag #NAME_CLIENT# ).
The issue I came across was then if you want to display mutiple rows with multiple columns where the number of rows could be many (like select * from employee) this replace functionality will not do.
This blog is an extension of my previous blog and describes a way to display this table data in a Word document. For this we also need to use the PL/SQL Utility Library “Alexandria".

Setup

What we need:

1) Word-document in docx format (Microsoft Word version 2007 and higher).
2) Several Oracle packages from the PL/SQL library Alexandria (https://github.com/mortenbra/alexandria-plsql-utils).
3) Client machine must have Word installed.
4) Word template must have been saved in a database tabel as a blob.

See also my previous blog about these points.

Modify Alexandria packages

A Word table is in coding very similiar to structure to a html table, it has table, row and cell structure information. Since we need lots of coding to make such a table in word, we need a large variable to contain all the Word table structure and PL/SQL table data.
What we want in the end is this: replacing a tag in the Word template with the created Word table.

The best way to do this is by putting all this data in a CLOB variable. So we need to create an new type in the database:

create or replace TYPE "T_CLOB_ARRAY"   is TABLE OF CLOB;

For all this to work we make to make some adjustments to the standard replace functionality.
We need to modify the Alexandria function called OOXML_UTIL_PKG.get_file_from_template.
We need to add 2 new in parameters with an default in-value for (that way other procedures calling this function will continue to work).
We also need to add a call to a new function called string_util_pkg.multi_replace_clob, just above the existing call to multi_replace (see also next paragraph)

function get_file_from_template 
     ( p_template in blob
     , p_names in t_str_array
     , p_values in t_str_array
     , p_names_clob  in t_clob_array DEFAULT t_clob_array()  -- new in parameter
     , p_values_clob in t_clob_array DEFAULT t_clob_array()   -- new in parameter
     ) return blob
as
  l_file_list                    zip_util_pkg.t_file_list;
  l_docx                         blob;
  l_blob                         blob;
  l_clob                         clob;
  l_returnvalue                  blob;
begin
  /*
  Purpose:      performs substitutions on a template
  Remarks:      template file can be Word (docx), Excel (xlsx), or Powerpoint (pptx)
*/
  l_file_list := zip_util_pkg.get_file_list (p_template);
  for i in 1 .. l_file_list.count loop
    l_blob := zip_util_pkg.get_file (p_template, l_file_list(i));
    if l_file_list(i) in
        ( 'word/document.xml'
        , 'word/header1.xml' 
        , 'word/footer1.xml' 
        , 'xl/sharedStrings.xml') or 
       (l_file_list(i) like 'ppt/slides/slide%.xml') 
   then
      l_clob := sql_util_pkg.blob_to_clob (l_blob); 

      -- **** New function call *******
      l_clob := string_util_pkg.multi_replace_clob 
                       ( l_clob
                      , p_names_clob
                      , p_values_clob
                      ); -- this has been added
       -- **** End New function call *******

      l_clob := string_util_pkg.multi_replace 
                      ( l_clob
                      , p_names
                     , p_values); -- for tekst array
     l_blob := sql_util_pkg.clob_to_blob (l_clob);
    end if;
    zip_util_pkg.add_file (l_returnvalue, l_file_list(i), l_blob);
  end loop;
  zip_util_pkg.finish_zip (l_returnvalue);
  return l_returnvalue;
end get_file_from_template;

We also need to extend the Alexandria package STRING_UTIL_PKG with a new function body (and off course header) called multi_replace_clob.
This function will replace the tags in the Word-document with the clob variable containing the word table.

function multi_replace_clob ( p_clob in clob
                            , p_search_for in t_clob_array
                            , p_replace_with in t_clob_array
                            ) return clob
as
  l_returnvalue clob;
 --
  -- Deze function is needed if a clob > 32k is used to replace in a clob 
  function replace_clob( in_source IN CLOB
                       , in_search IN VARCHAR2
                       , in_replace IN CLOB
                       ) RETURN CLOB
  IS
    l_pos pls_integer;
  BEGIN
    l_pos := instr(in_source, in_search);
    IF l_pos > 0 THEN
      RETURN substr(in_source, 1, l_pos-1)
             || in_replace
             || substr(in_source, l_pos+LENGTH(in_search));
    END IF;
    RETURN in_source;
  END replace_clob;    
  --  
begin
  --
  l_returnvalue := p_clob;
  if p_search_for.count > 0 then
    for i in 1 .. p_search_for.count 
    loop 
      l_returnvalue := replace_clob (l_returnvalue, p_search_for(i), p_replace_with(i));
    end loop;
  end if;
  --
  return l_returnvalue;
  --
end multi_replace_clob;

A table cell in Word

A table is nothing more then multiple rows containing multiple cells.

I have created a function that returns the Word equivalent of a table cell. For each cell you can modify the width, and if you want to add a certain format (bold, italic and/or underscore).

function add_td ( p_parameter in varchar2
                  , p_width   in number default 2880 --5000 --
                  , p_format    in varchar2 default ''
                  ) return varchar2 
    -- Function used to return data as a Word table cell tabel cell
    -- Programmer may also add the width of the cel, but make sure all cells in the column
    --    are the same width ( so for instance also the header )
    -- Programmer may choose to make the  bold, underscore or italic ( or a combination of those )
  is  
    --  l_style determines the font, size and format of the cell
    -- Tohama is chosen because its best readable in small fonts
    -- A fontsize of 16 here means in Word de height will be 8
    l_style varchar2(4000) := 
        '<w:rPr>'||
             CASE 
                WHEN instr(p_format, 'b') > 0 
                  then '<w:b w:val="true"/>' 
                else null 
             end ||
             CASE 
               WHEN instr(p_format, 'u') > 0
                 then '<w:u w:val="single"/>' 
               else null 
             end ||
             CASE 
               WHEN instr(p_format, 'i') > 0 
                  then '<w:i w:val="single"/>' 
               else null 
             end ||
             '<w:rFonts w:ascii="Tahoma" w:hAnsi="Times New Roman" w:cs="Times New Roman"/><w:sz w:val="16"/></w:rPr>';
  begin
    return '<w:tc>'||
             '<w:tcPr><w:tcW w:w="'||p_width||'" w:type="dxa"/></w:tcPr>'||
               '<w:p>'||
                 '<w:r>'||
                   l_style ||
                 '<w:t>'||p_parameter||'</w:t>'||
               '</w:r>'||
             '</w:p>'||
           '</w:tc>';

  end add_td;

A Word table in a clob

This function returns the employee data in the form of a Word table. Each column value of the Oracle table calls the add_td function, encasing the data in a Word cell makeup. We must make sure to add the row and table Word markings to complete the Word table.

  function get_employee_data  return CLOB
  is
    cursor c_emp is
      select emp.id, emp.name, emp.sexe, emp.address, emp.city
         from employee emp
    --
    l_emp CLOB;
    l_table_borders varchar2(4000);
   l_width number := 2800;

begin 
  -- the borders of the table
  l_table_borders := 
    '<w:tblPr>'||
      '<w:tblBorders>'||
        '<w:top w:val="single" w:sz="12" w:space="0" w:color="000000" />'||
        '<w:start w:val="single" w:sz="12" w:space="0" w:color="000000" />'||
        '<w:bottom w:val="single" w:sz="12" w:space="0" w:color="000000" />'||
        '<w:end w:val="single" w:sz="12" w:space="0" w:color="000000" />'||
        '<w:insideH w:val="single" w:sz="12" w:space="0" w:color="000000" />'||
        '<w:insideV w:val="single" w:sz="12" w:space="0" w:color="000000" />'||
      '</w:tblBorders>'||
    '</w:tblPr>';
  --
  l_emp:= '<w:tbl>'; -- The start of the table
  --
  -- the header of the table
  l_emp:= l_emp || 
    '<w:tr><w:trPr><w:trHeight w:val="200" w:hRule="exact"/></w:trPr>'||
             add_td('ID', l_width, 'bu')||
             add_td('Name', l_width, 'bu')||
             add_td('Sexe', 500, 'bu')||
             add_td('Address', l_width, 'bu')||
             add_td('City', l_width, 'bu')||
    '</w:tr>';    
  --
  -- we want the borders on the employee data, not on the header   
  l_emp:= l_ emp ||  l_table_borders; 
  for j in c_emp 
  loop
    -- this constitues 1 row on the table
    l_emp:= l_emp || 
      '<w:tr><w:trPr><w:trHeight w:val="200" w:hRule="exact"/></w:trPr>'||
            add_td(j.id)||
            add_td(j.name)||
            add_td(j.sexe)||
            add_td(j.address)||
            add_td(j.City)||
      '</w:tr>';      
    end loop;  
    l_ emp := l_emp || '</w:tbl>';  -- The end of the table
    --
    return l_ emp;
    --
  end get_employee_data;

Make the document

In this procedure we retrieve the Word template from the database and replace the tags in the template with data.
There is one tag (#EMPLOYEES#) that is being replaced by a CLOB, and two normal tags (#BUSINESS# and #DATE#) that are being replaced with text.

Create a screen in Apex and add a button that calls this procedure:

procedure make_document
declare
    CURSOR c_template ( cp_template_name varchar2) IS
      SELECT filename
           , content as data
           , description
           , mime_type
           , character_set
        FROM mv_documents
       WHERE description = cp_template_name;
    r_template c_template%ROWTYPE;
   --
    l_names_string  gvg_owner.T_STR_ARRAY := gvg_owner.T_STR_ARRAY();
    l_values_string gvg_owner.T_STR_ARRAY := gvg_owner.T_STR_ARRAY();  
    --
    l_names_clob   gvg_owner.t_clob_array := gvg_owner.t_clob_array();
    l_values_clob  gvg_owner.t_clob_array := gvg_owner.t_clob_array();
    --
    l_new_file blob;
    l_new_file_naam varchar2(200);
    l_clob         clob;
begin
    -- get the word template from the table
    OPEN c_template ( 'employee_template' );
    FETCH c_template INTO r_template;
    CLOSE c_template;
    --
    l_names_clob.EXTEND(1);
    l_values_clob.EXTEND(1);
    --
    l_clob := get_employee_data;
    l_names_clob(1) := '#EMPLOYEES#';
    l_values_clob(1) := l_clob;

   -- add some simple tekst replacements
    l_names_string.extend(2);
    l_values_string.extend(2);
    l_names_string(1)  := '#BUSSINESS#';
    l_values_string(1) := 'Integration & Application Talents';
    l_names_string(2)  := '#DATE#';
    l_values_string(2) := to_char(sysdate,’DD-MM-YYYY’);     
    -- 
    l_new_file := ooxml_util_pkg.get_file_from_template 
                          ( r_template.data
                          , l_names_string
                          , l_values_string
                          , l_names_clob
                          , l_values_clob );
    l_new_file_naam := 'Employees.docx';

    l_length := DBMS_LOB.GETLENGTH( l_new_file);
    owa_util.mime_header( r_template.mime_type, FALSE );
    htp.p('Content-length: ' || l_length);
    htp.p('Content-Disposition:  attachment; filename="'||l_new_file_name||'"');
    owa_util.http_header_close;
 
    wpg_docload.download_file( l_new_file );
end make_document;

The Word docx template

The result of the replacement

Conclusion

With few adjustments to the Alexandria packages you can enrich your documents with a table in Word displaying your PL/SQL data from the database.

The only disadvantage is that for each new table you wish to display, you will need to create a procedure. But with simple cut and paste of the code that won't take much time.