Proposal Automation Software

Oracle PDF Document Automation

Oracle PDF


In every business, database plays an important role in holding entire business data. Major vendors, 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.


Some businesses such as Insurers, health care institutions, financial institutions etc. have a heavy dependence on documents. Document creation from database records is very important for them. Manual creation of these documents from database data is a costly and time-consuming task. Automating this document generation saves a ton of time and resources for enterprises.


The most popular option for Oracle PDF document automation is to use PL/SQL libraries or coding the templates for PDF generation. 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 team. 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.


Business users can create or edit contracts, reports, letters templates in their favorite Microsoft Word or PDF editors and use EDocGen to generate documents by merging them with Oracle database data.


Oracle PDF Documents from XML data


Oracle PDF 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 you can 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. It supports filling of dynamic text, tables, content blocks, hyperlinks and images from XML data into document templates. Please visit XML to PDF 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 PDF documents in zip format.

You can automate these steps by integrating with EDocGen API.

Oracle PDF Documents from JSON data


Oracle PDF 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 you can 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

  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.

img


Step2: Populating JSON into Document Template

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

1. Add relevant tags for dynamic text, table, images, paragraphs, hyperlinks etc. in the existing template and upload to EDocGen

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 PDF documents and allows you to download them in ZIP format.



EDocGen is the 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