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 the "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;

The system supports the creation of tables, loops, and lists from Oracle data. You can also create nested tables up to 6 levels. If your template has many tables, nested tables, lists, etc., then fetching data in JSON and XML formats through SQL queries is a more elegant approach. Take the example of "Order table".

Below is the query that populates this table. It retrieves the data in JSON format.

SELECT
  JSON_Query(
    CustomerID,
    CustomerName,
    (
      SELECT
        SalesOrderID,
        Status
      FROM
        salesOrderHeader AS D
      WHERE
        H.CustomerID = D.CustomerID
    )
  ) as value
FROM
  Customer AS H
 

Oracle DB also allows you to retrieve data in XML format. Below is the corresponding query that fetches XML data.

SELECT
  XMLForest(
    CustomerID,
    CustomerName,
    (
      SELECT
        SalesOrderID,
        Status
      FROM
        salesOrderHeader AS D
      WHERE
        H.CustomerID = D.CustomerID
    )
  )
FROM
  Customer AS H

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.

Automation

Entering the information each time you produce documents is time-consuming and tedious if you are using the same template and query. The system provides options to automate these steps.

Follow the above steps to create an automation. After that, you can create documents with a click of a button.

Also, the system offers options to schedule the generation every day/month/year.

There is also an option to automatically upload printer-friendly PCL5/PostScript files into the FTP server.

EDocGen is an API-first product and you can easily integrate it into your IT environment. It 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.