Oracle PDF Document Automation

Oracle PDF

In every business, the database plays an important role in holding entire business data. Oracle DB is the most popular and is widely used in enterprises. Some businesses such as Insurers, health care, financial institutions, etc. have a heavy dependence on documents. Document creation from database records is very important for them. Automating this document generation saves a ton of time and resources for enterprises.

The most popular options for Oracle PDF document automation are

1. Use of PL/SQL libraries.
2. Coding the templates and document generation in a programming language like Java.

Both these options need development resources and are expensive. Moreover, document templates undergo frequent changes, and getting a developer’s time for making associated changes is difficult. This forces business users to ever dependent on resource-constrained IT teams. Hence business users need a DIY Oracle PDF document creation system, wherein they can modify the templates themselves.

EDocGen is the perfect solution for them. They can create or edit contracts, reports, and letter templates in their favorite Microsoft Word or PDF editors. They then can use the system to generate PDF documents in bulk from Oracle data.

Oracle PDF Documents - EDocGen

The system generates documents by populating Oracle data into templates. Oracle PDF document creation is a 3-step process.

1. Connect to Oracle database.
2. Populate Oracle data into a template, generating PDF documents in bulk.
3. Optionally, send generated documents to individual recipients by email or for e-Sign.

Step1: Connect to the Oracle DB

Enter the connection URL in "username@hostname[:port]/DatabaseName" format, password, and the native SQL query. Writing the query is a one-time job and SQL can be changed easily whenever the template changes. System admins (DBA) and many business users are adept at SQL.

Below are the sample MSWord invoice template and associated query. The system populates the table, image, and dynamic text fields in 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;

If your template has a lot of tables and nested tables, SQL query to retrieve the data in XML or JSON format is more elegant.

XML from Oracle DB

Oracle DB allows you to retrieve data in XML format. The following examples explain how to query for XML data. 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.

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.

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.

Step2: PDF generation

The system supports the filling of dynamic text, tables, content blocks, hyperlinks, and images in the template with Oracle data.

Business users can populate data as-is. They can map template tag names with SQL columns, thus controlling the data you wish to show on your generated documents. If both names match, the system automatically maps them for you. In the above example, you would notice that the template's tag names match with database column names. Thus, database changes would not affect document generation.

After mapping, select PDF as the output format and click the generate button. You can also encrypt the PDFs and include watermarks.

Step3: Document Distribution

This is an optional step. The system allows you to send generated documents to the recipient from your business email. You can also personalize the email with a custom email subject and email body. Apart from Email, the system supports other distribution channels including e-Sign, print, sync to cloud storage, and document archival.

You can automate these steps by integrating with EDocGen API. EDocGen is a versatile solution for Oracle PDF document automation. You can generate Word documents as well along with PDF documents. Register and try for yourself.