Oracle Word - Word docs generation from Oracle DB

Oracle Word

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

For organizations that create a lot of documents, manual creation of documents from database records is a costly and time-consuming task. The most popular options for Oracle word document automation are - expensive reporting tools, or custom development using code libraries.

They are not business useri-friendly and are maintenance-heavy. Document templates undergo changes and getting a developer’s time for corresponding code changes is difficult and is expensive.

Hence, business users require a DIY Oracle word document creation system, and here comes EDocGen. It helps them to create contracts, reports, letters, etc. from existing Microsoft Word and PDF templates. They can populate Oracle database data into these templates to create business documents.

Oracle Word Demo

Oracle to MS Word Automation

Following section details how business users can generate documents themselves without IT support. Generation of Docx from Oracle database is a simple 2-step process.

1. Connect to your database from the system.
2. Generate documents from query data.
3. Optionally, you can distribute generated documents to individual recipients.

Step1: Connect to the database

In the connection screen, select the Oracle database from the drop-down. Then enter the connection URL in "username@hostname[:port]/DatabaseName" format and the password. The system connects to both local and cloud databases. For a local database, your connection URL would look like root@localhost/oracledb.

For the SELECT query, use native SQL query statements. The sample MSWord template is provided along with the associated query for populating it. The system fills in the table, image, and dynamic text fields within the template.

SELECT
  invoice.inv_no,
  `logo`,
  `date`,
  `trms_pymnt`,
  `po_ref`,
  `name`,
  `address`,
  `contact`,
  `Email`,
  `Phone`,
  `myhtml`,
  para,
  CONCAT(
    '[',
    GROUP_CONCAT(
      CONCAT(
        '{"amnt":"',
        amnt,
        '", "descrptn":"',
        descrptn,
        '"}'
      )
    ),
    ']'
  ) IT
FROM
  `invoice`,
  `inv_item`
WHERE
  invoice.inv_no = inv_item.inv_no
Group BY
  invoice.inv_no;

Enter all mandatory fields and click on the "Connect" button to check the connection. After a successful connection, the next step is to populate the query data into the Word document template.

If you don't want to enter connection details for security reasons, you may download the Oracle data in XML/JSON format and use it for document generation. The next section details how to create XML/JSON data from Oracle DB and how to populate them in templates for documents creation.

Step2: Generate Documents

The system supports the dynamic population of text, tables, paragraphs, hyperlinks, blobs, images, etc. You can populate the query data to create simple to very complex documents.

Business users can populate query data as-is without any data transformation. They have an option to map the template's dynamic fields to the query columns. If both the names match, the system automatically maps the fields. Thus, database changes don't affect document creation.

Enter the file name and prefix for individual documents before clicking the generate button. The output in ZIP format would contain generated documents

Step3: Document Distribution

You have an option to send generated documents individually by mail as an attachment. Select the email column in the query, input your mail subject & body, and click the send button. The system auto-sends these emails from your business email.

Other options include syncing the documents to cloud storage or sending them for E-Sign.

Automation

Inputting connection and query information for every generation can be quite time-consuming and monotonous. The system offers functionality to automate these steps.

To create automation, follow the above steps. After that, the generation of Word documents is as simple as clicking a button.

You can also choose to generate printer-friendly PCL5 and PostScript files automatically and then transfer them to an FTP server.

Docs from Oracle without Credentials

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 for template changes. System admins (DBA) and many business users are adept at SQL. The following examples explain how to export Oracle database data as XML.

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

Execution of this query shows the following two records.

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);

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. The next step is to populate this XML into the document template. Please refer to XML to Word for how to go about the XML population.

Exporting JSON from Oracle DB

Since Oracle Database release 12.2 Oracle allow the creation of JSON data using 4 new SQL operators

1. JSON_OBJECT
2. JSON_OBJECTAGG
3. JSON_ARRAY
4. JSON_ARRAYAGG

A combination of these operators can create a complex JSON instance.

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

Populating JSON/XML into Document Template

Please visit JSON to Word and JSON to Word 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.

Apart from the web app, EDocGen also offers REST API for Oracle to Word generation. By integrating with API, you can automate document generation without user intervention (on-demand document generation). Thus you can

1. Create document workflows.
2. Schedule document creation and distribution.

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