In this article

Summarize and analyze this article with:

Fredrick Harleman Content Writer | Published: (Updated: )

Oracle PDF

In every business, the database plays an important role in holding all of the 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 to make 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.

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

In SQL queries, you can use inner joins and outer joins.


SELECT
    i.inv_no,
    i.logo,
    i."date",
    i.trms_pymnt,
    i.po_ref,
    i.name,
    i.address,
    i.contact,
    i.Email,
    i.Phone,
    i.myhtml,
    i.para,
    j.items
FROM invoice i
JOIN (
    SELECT 
        inv_no,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                KEY 'amnt' VALUE amnt,
                KEY 'descrptn' VALUE descrptn
            )
        ) AS items
    FROM inv_item
    GROUP BY inv_no
) j ON i.inv_no = j.inv_no;

Tables

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".

Sales Order

Order Status

 12345

 5

 --

 --

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

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 will notice that the template's tag names match the 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

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.

SQL Document Automation

The system provides the functionalities to pull data from multiple sources—including databases (like SQL, Oracle, and MongoDB) and CRMs (like Salesforce and Dynamics 365)—and seamlessly merges the data for document generation.

SQL Document Automation

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

SQL Document Automation

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

SQL Document Automation

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.

Frequently Asked Questions

Why should enterprises replace PL/SQL-based Oracle PDF generation with EDocGen?

PL/SQL libraries and Java-based Oracle PDF generation require development resources and are expensive to maintain. Document templates change frequently, and every change requires a developer's time, creating a bottleneck for business teams. EDocGen eliminates this dependency by letting business users create and edit templates directly in Microsoft Word or PDF editors, then generate PDFs from Oracle data themselves — with no code changes needed.

How do I connect EDocGen to an Oracle database?

Enter the Oracle connection URL in 'username@hostname[:port]/DatabaseName' format along with your password in EDocGen's connection screen. Then enter your native Oracle SQL SELECT query. The connection and query are a one-time setup and can be saved as an automation workflow for recurring use.

Does EDocGen support complex Oracle SQL queries for document generation?

Yes. EDocGen accepts native Oracle SQL queries including INNER JOIN, OUTER JOIN, and subqueries. For templates with tables or nested sections, using JSON_ARRAYAGG with JSON_OBJECT or XMLForest constructs to return structured JSON or XML data from Oracle is the recommended approach, as it allows EDocGen to correctly map and populate multi-row and nested data into the template.

Does EDocGen support nested table generation from Oracle data?

Yes. EDocGen supports nested table generation up to 6 levels from Oracle data. For templates with multiple or deeply nested tables and lists, fetching data in JSON or XML format via SQL query constructs such as JSON_ARRAYAGG or XMLForest is the recommended approach.

What output formats does EDocGen support for Oracle document generation?

EDocGen primarily generates PDF and Word (DOCX) documents from Oracle data. For PDF output, options to add password protection and watermarks are available. All generated documents are packaged into a ZIP file for download.

Can EDocGen merge data from Oracle and other databases or CRMs into a single document?

Yes. EDocGen's automation feature can simultaneously pull data from multiple sources — Oracle, SQL Server, MongoDB, Salesforce, Dynamics 365 — across different schemas and databases, and merge them into a single document template without any pre-processing or ETL pipeline.

Can I schedule Oracle PDF document generation to run automatically?

Yes. EDocGen supports scheduled document generation daily, monthly, or yearly. Once an automation workflow is configured with the Oracle connection, query, and template, no manual intervention is needed for subsequent runs. The system can also auto-generate printer-ready PCL5 and PostScript files and upload them to an FTP server.

Can I pull and dynamically embed images stored within an Oracle database?

Yes. If your database references images as direct file URLs or houses them as raw Base64 data blocks inside BLOB columns, EDocGen retrieves and renders them directly within your layout fields during creation runs.

Is there an API to automate Oracle PDF generation programmatically?

Yes. EDocGen is an API-first product with a REST API that enables programmatic Oracle to PDF and Word document generation. It integrates into any IT environment and supports on-demand and bulk document generation workflows triggered directly from Oracle database events or external systems.

Found what you’re looking for?

Start generating the documents with us.

Book a demo