Generating PDF and Word Documents from an APEX application

Generating PDF and Word documents from an APEX application is a request that we get all the time. There are many great third party tools to help you do this but in this blog post I’m going to focus on PL/PDF, which is the tool that I used most recently for customer work.

PL/PDF is a set of PL/SQL packages that you can install into your database. You are then able to call these packages from within your applications to generate PDF and Word documents. You can either create documents directly using the packages available or you can upload a template document to generate a custom package based on your preferred layout.

The application I was building required users to log on and fill out a web form, which was then printed as a PDF containing the data they had entered. Certain fields were also looked up from data in the database. The output layout was created in Word to match existing hard-copy documents.

In this blog I am going to talk you through an extremely simplified version of the development process, from creating a template in Word to generating the output.

A simple document that you might want to recreate in an application could look something like this:

You can replicate this example document in an application with a screen like this. The same fields are available with users able to enter data into certain places:

We are using APEX items to represent the fields on the output document and a classic report to show the job title history from the database.

So now we need to use our document generation tool of choice (in this case PL/PDF) to link the data in our APEX application with a Word template based on the original document. We are essentially going to take our example output and add tags where we want our data to appear. You then upload this file to the database and PL/PDF will convert the document into PL/SQL code.

PL/PDF have lots of detailed tutorials online and include example templates in the trail software download. Here I’m going to show you how to do this really quickly using APEX items and collections. I would definitely recommend working through the tutorials if you are going to develop your own template based documents.

First you need to take a copy of your form and switch to Developer mode. From here you will be adding tags to your document so that it looks like this:

The expression tag can just use the substitution to take the value of the page item straight from your APEX application page like this:

The job history table relies on a cursor being declared in the specification of the package that is going to generate your document. We can specify these as follows:

PL/SQL is entered in to the Tag field. This is then included in the package that PL/PDF generates at the appropriate place. C_job_history(above) references a cursor that you have manually specified in the package specification for your custom PDF code. This is required when defining a Group. This cursor will contain the data from your application that you want in the PDF. F_ITEM.job (below) is referencing the job value in this cursor to be printed in the column.

Once you have filled in your template with substitutions, you’re ready to run it through PL/PDF. The online tutorials will talk you through the steps to combine your template with a package specification containing your cursors. What you get is a dedicated PL/SQL package that generates your form straight from a function call. When you call the function you are returned a BLOB of the PDF/Word version of your form which you can then do what you like with – §email, download or store in the database.

Once you have the package you can even modify it yourself as it is all just PL/SQL code calling the PL/PDF packages, however it is probably just as easy to re-do your template and generate another package.

This was a really simple example, but you can take this much further, for example using images to create branded PDF or Word files from database or application form data or adding headers and footers to the document. You can end up with perfectly branded and laid out documents which you can generate really easily from your APEX applications.

Author: Craig Sykes 

Job Title: Senior Oracle Development Consultant

Bio: Craig is a Senior Development Consultant at DSP-Explorer. Craig has an MSc in Computing Science and is an experienced software engineer, utilising development tools such as PL/SQL and APEX to provide bespoke ERP software to both UK and international businesses. Craig has experience developing solutions to connect Oracle systems to a wide range of existing external applications within business environments.