Proposal Automation Software

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.


Oracle Database Connection

Below are the sample MSWord invoice template and associated query. The system populates the table, image, and dynamic text fields in the template.


Word template for SQL

SQL tables

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.

XML from Oracle SQL

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);
XML from Oracle SQL

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.

SELECT JSON_ARRAY(first, last) FROM customers;

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.

JSON from Oracle SQL


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.


Oracle field mapping

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

SQL PDF generation



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.


SQL Document distribution


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.

Popular Posts