Creating PDFs in APEX after ORDS 19.1

Until 19.1 ORDS provided a built-in printing engine based on Apache FOP which allowed you to download a PDF version of your reports and XLS-FO templates in a very easy manner. However in ORDS 18.4.0 release notes we could find information that this feature is deprecated and will be removed in future release. This is exactly what happened with the release of ORDS 19.2.

So what actually happened? 

This is from Oracle’s release notes of ORDS 19.2:

“Deprecation of Apache FOP PDF Support
Support for generating PDF responses for PL/SQL Gateway calls will be removed in ORDS 19.2.0. This will impact the features in Oracle Application Express relating to generating PDF documents. Future versions of Oracle Application Express will move to a new mechanism to generate PDF resources.”

This means that Apache FOP will not be available anymore in your ORDS installations. This may certainly cause problems for users, which were using this feature intensively, especially taking into account that at the time of writing this post there’s unfortunately still no new mechanism for generating PDFs pre-shipped. When we try to print a PDF, a very disappointing message is displayed: 

However, fear not, the solution may turn out to be not too complex after all. 

So, what are my choices?

There’s AOP  - Apex Office Print https://www.apexofficeprint.com/index.html#pricing, but in the free tier you get to generate only 30 reports per month. The most expensive plans go up to $350 and 35000 reports per month. It’s a PLSQL package, so the transition will not be completely transparent to the application designers.

A similar package is PL/PDF available here: https://www.plpdf.com. It has a different pricing scheme, where there’s no limit on the number of reports generated. 

Another option is Oracle BI Publisher, which is also a paid solution. It can cover many different use cases and possibility to use it as a print server is only a very small part of its capabilities. This is reflected in the cost of the license, however, if you already have Oracle BI as a part of your infrastructure, this might be the way to go.

However...

These options, apart from being paid solutions, require from the APEX developers to put in time and effort - either some PL/SQL code has to be written, or the template has to be recreated.

Third option and the one I will cover in this post that solves these problems, is a self-hosted, external Apache FOP engine. It might sound complicated, but I will show you how this can be achieved in a couple of easy steps. The biggest challenge here was guessing in what format ORDS sends the data to the print server when auto-generating PDFs, but you don’t have to worry about this — it only took some trial-and-error:

In our case, we’re going to host our Apache FOP on a Tomcat, just like our ORDS, creating a solution that is self-contained, seamless to the developers and free! Nothing stops you from doing  the same on a separate/different application server. It may even end up being an easier to maintain solution for one important reason that I will mention later. 

Step one - setting up the Apache FOP

If you want to just be done with this step, you can use the precooked fop.war that you can find at the end of this post. If not - read on!

Download the FOP source from https://xmlgraphics.apache.org/fop/download.html. 

Since imitation is the highest form of flattery, let’s pay some compliments to the author of one the bundled examples.

Navigate to fop-core/src/main/java/org/apache/fop/servlet and open FopServlet.java in your editor/IDE of choice.

ORDS makes a POST request with 2 parameters - “template” and “xml”. Their names are pretty self-explanatory and both of them contain the data in plain text. Knowing that, there are only a couple of changes needed in the servlet:

1. Change XSLT_REQUEST_PARAM value to “template”

2. Rename the method “doGet” to “doPost”

3. Modify the “convertString2Source” method so it only contains 1 line:​

return new StreamSource(new java.io.StringReader(param));

With that ready, it’s time to compile the sources. Download ant if you don’t already have it: https://ant.apache.org/bindownload.cgi

Navigate to the FOP directory in the console and simply execute: ant

The fop.war should now be present in fop/build directory.

As a nice and quick testing scenario I always use Tim Hall’s ORDS in Docker - https://oracle-base.com/articles/linux/docker-oracle-rest-data-services-ords-on-docker. It’s perfect, as a minimalistic way to test if our configuration works. Just mount the fop.war into /u01/config/instance1/webapps/fop.war

Step two - setting up ORDS

First, you have to login to the internal workspace in APEX.

In order to do that, navigate to the main APEX page (in our case it will be localhost:8080/ords). As a name of the workspace put INTERNAL and then the credentials to access the internal workspace (for us it’s admin/ApexPassword1).

Go to “Manage Instance” menu, then “Instance Settings” and to the “Report Printing” tab.

There, you should set following values:

Print Server: External (Apache FOP)

Host Address: be careful here, especially if you’re running in docker — take the container’s hostname:

docker inspect --format='{{.Config.Hostname}}' <<container_name>>

Step three - enjoy!

That was simple, wasn’t it? Now you can go back to printing beautiful reports for free and without your users even noticing any change!

Why move to a new version of ORDS and put in all of this effort?

Well, of course there are bugs patches introduced in every new release. But what makes moving to 19.4 worthwhile for me is the introduction of new features, like the SQL Developer Web - a quick way for your users to manage their’s data from the browser, without the hassle of taking care of TNS Names, tunneling and other access issues. That Jeff Smith and Tim Hall from Oracle-Base have nice posts about it if you're interested. The REST aspect of ORDS is getting generous improvements as well — like the connection recycling, which may significantly increase the throughput of the processed requests.

Update: APEX 20.1 limited PDF printing support

Reading through APEX 20.1 release notes there's mention of:

Native PDF Printing for Interactive Grid

You can now print PDF files directly from Interactive Grids. This feature produces a PDF file which includes formatting options such as highlighting, column grouping, and column breaks.

I have given it a go and indeed it seems to work. But as mentioned, it's only for Interactive Grids. When using e.g. Interactive Report, we're greeted with the same, old 503 as before. Using Apache FOP as described in this post seems like a more reliable option for the moment, but this feature shows that things are on a good course and maybe we'll get full support for built-in PDF printing.

Another small thing I noticed is support of Apex Office Print as a Print Server directly in the instance setting. So if you don't mind spending some money, it looks like a straightforward alternative as well: