Proposal Automation Software

Oracle Word Document Automation

Oracle Word


In every organization, database plays an important role in holding entire organization’s data across functions/departments. IBM, Microsoft & Oracle control close to 89% of DBMS market with Oracle leading the pack with >40% of the total database market. It is widely used in enterprise applications.


For organizations creating state-specific/country-specific documents such as insurers, health care institutions and financial institutions etc., manual creation of documents from database records is a costly and time-consuming task. The document templates undergo a frequent change that forces associated code changes every time.


While SQL Server users have the luxury of Zapier integrations for connecting to document automation/document generation systems, Oracle users don’t have that option. Hence the most popular option for Oracle word document automation is to rely on the development team. Document templates undergo frequent changes and getting a developer’s time is difficult and is expensive. Hence business users require a DIY Oracle word document creation system and here comes EDocGen.


Business users can create or edit templates such as contracts, reports, letters etc. in Microsoft Word and then use EDocGen to generate documents by merging them with the data from your Oracle database.


Oracle Word Documents from XML data


Oracle word document creation using EDocGen is a 2-step process.

  1. Exporting Oracle DB data into XML
  2. Populating that XML into document template using EDocGen

Either these steps can be carried out sequentially or automate them by integrating with EDocGen API. You can map XML elements to dynamic field tags of Document thus controlling the data you wish to show on your generated documents.

Step1: Exporting XML from Oracle DB

SQL allows you to retrieve Oracle DB data in XML format. Writing SQL is a one time job and SQL can be changed easily whenever template changes. System admins (DBA) and many business users are adept at SQL. Following examples explain how to export Oracle database data as XML. I took this example from Gert Poel's blog post.


SELECT XMLElement( "DEPARTMENT"
                 , department_name
                 ) 
FROM departments
WHERE department_id IN (10, 20);

Execution of this query shows the following two records.

img

To generate multiple XML Elements we use XMLForest.

SELECT XMLForest(department_id as "ID"
                , department_name as "NAME"
                )
  FROM departments
 WHERE department_id IN (10, 20);
img

Using XMLAgg we can retrieve one row with all records from the query.

ELECT XMLAgg(XMLElement("DEPARTMENT"
                        , XMLAttributes( department_id as "ID"
                                       )
                        , department_name
                        )
             )
  FROM departments
 WHERE department_id IN (10, 20);

<DEPARTMENT ID="10">Administration</DEPARTMENT><DEPARTMENT ID="20">Marketing</DEPARTMENT>

Apart from this, there is also native support for XML in Oracle database. Oracle XML DB is an out-of-the-box solution for storing and retrieving XML documents within Oracle. This provides a high-performance, native XML storage, and retrieval.


Now our XML is ready. Next step is to populate this XML into document template using EDocGen


Step2: Populating XML into Document Template

EDocGen is Windows OpenXML based solution that supports filling of dynamic text, tables, content blocks, hyperlinks and images from XML data into document templates. Please visit XML to Word blog for a detailed description of how to go about XML population. Briefly,

  1. Upload existing template after adding tags for dynamic fields. EDocGen uses these tags to identify the dynamic sections of the template
  2. Upload XML and map XML elements to template's dynamic fields. Thus no data transformation is necessary. Click generate button to create bulk word documents in zip format.

You can automate these steps by integrating with EDocGen API.

Oracle Word Documents from JSON data


Oracle word document generation with JSON is a 2-step process.

  1. Exporting Oracle DB data into JSON
  2. Populating that JSON into document template using EDocGen

Either these steps can be carried out sequentially or automate them by integrating with EDocGen API. You can map JSON keys to dynamic field tags of Document thus controlling the data you wish to show on your generated documents.

Step1: Exporting JSON from Oracle DB

Since Oracle Database release 12.2 Oracle allow the creation of JSON data using 4 new SQL operators JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, JSON_ARRAYAGG. A combination of these operators can create a complex JSON instance.

SELECT JSON_ARRAY(first, last) FROM customers;

Oracle also offers APIs called SODA (Simple Oracle Document Access) that are built on top of the JSON functionality, and deliver the experience of a NoSQL document store with the Oracle RDBMS.

img


Step2: Populating JSON into Document Template

Please visit JSON to Word blog for a detailed description of how to go about JSON population. Briefly,

1. Upload existing template after adding relevant tags for dynamic text, table, images, paragraphs, hyperlinks etc.

2. Populate it with JSON by uploading JSON and map dynamic fields to JSON keys. Thus, no data transformation of JSON is needed. EDocGen generates Word documents and allows you to download them in ZIP format.



EDocGen is the versatile solution for Oracle word document automation. You can generate PDF documents as well along with Word documents. Register and try for yourself.

Popular Posts