Friday, April 26, 2013

PDF Printing with Oracle Application Express 4.2.2

Oracle Application Express 4.2.2 was released today, you can
download the full release from the Download page on OTN. As always, there have
been numerous improvements and bug fixes. We’ve updated many of our included
packaged applications and we’ve introduced a new packaged application: the
Survey Builder, which you can use to easily create and run surveys.

With APEX 4.2.2 and the APEX Listener 2.0 we’re also introducing
a number of report printing enhancements. Using the APEX Listener for PDF
printing has been added as a third option in addition to using Apache FOP or
Oracle BI Publisher as external print rendering engine. The main difference in
using the APEX Listener for PDF printing is that the FOP libraries are actually
built into the Lister, thus no complicated configuration of an external engine
is required in this setup. All that’s required is to log into the APEX Instance
Administration, select the APEX Listener as the Print Server, and then enable
PDF printing for your Classic Reports, Interactive Reports or Report Queries.

And unless you’re calling the APEX Print APIs directly in
your custom PL/SQL code, you also won’t have to enable networking services in
the Oracle database anymore, because the APEX Listener based printing option loads your reports straight to the APEX Listener, which then renders them in
PDF format and downloads them to your browser.

To help you get started with using the APEX Listener for PDF
Printing I’ve created a short video that walks you through the configuration
steps in the APEX Instance Administration and then shows you how to create a
simple report with PDF export enabled:

We’ve also improved support for custom XSL-FO layouts in
APEX 4.2.2. This means, you can now more easily customize your PDF reports to
your requirements using third-party tools like Altova Stylevision or Stylus Studio that allow for drag and drop layout of XSLT stylesheets. So if you
have a Report Query or Classic Report Region, you can now simply download your
report data in XML format and use this XML data as the data source for any of
these tools. Once loaded into the tool, all components of your report data, including
information about your application, page, report region, user name as well as
session state can be easily included in the layout via drag and drop. Once
you completed your layout, you can safe it as a XSLT 1.0 file and upload this
file into APEX as a report layout (under Shared Components). Now you can update
your report query or classic report region to use this custom layout when
exporting your report in PDF format.

I’ve created a second video, which walks your through all the
details. Starting with the sample app created in the first video, I’m showing
you how to export your report in XML format, load this XML file into Altova Stylevision or Stylus Studio, then I'm demonstrating how to use these tools to
create custom layouts and finally how to upload your completed layouts and use
them for your reports in APEX:

60 comments:

Hı Marc,Thanks alot for your demo, it is very clear how to create pdf docs in apex.But I have serious problem. when I use turkish characters in data or static labels pdf file does not open and gives dameged message in both methods xslt file or generic

Thank you, great article. But One big problem still exist with fop printing. Not english characters (ęąśćńźżó... and other). Without it in my country and probably in other half world, using FOP printing is non sense : (

Regarding your question about XMLType columns, those are not directly supported in report queries, i.e. a column of that type that's included as-is would not work. However you could extract any information you need and convert it into a supported type like varchar2 or number.

Not sure what could be causing your issues, typically you should find some errors in the log file if the APEX Listener. In some case, you also might find some error messages in your PDF file when opening the file in a standard text editor.

Hi Marc,Thank you for the demo !I tested with Stylus Studio and it works very good butit doesn't with Altova Stylevision. I followed yours instructions, I’m able to generate the XSL file and create the report layout in Apex, but when I try to download the pdf version in Apex I get this message in the console: "An error occurred while loading the PDF.PDF.JS Build: e22ee54Message: stream must have data"(Note: My generated XLS file is 1.0 version as you recommended.)

Mario, regarding your error, not sure what could be causing this. Have you been using the same XML with both tools? I would reduce complexity of your XSL to something minimal that works and then debug from there.

Hi Marc, Thanks for your answer!Yes, I've been using the same XML for both tools.I've tried also reducing complexity of my XLS but it doesn't work neither, it's weird (I followed the same procedure as the tutorial).

have you determined whether this is an issue related to running the APEX Listener on Glassfish, or perhaps an issue with the XSLT? I would suggest running the APEX Listener in stand-alone mode temporarily just for the purpose of determining whether this is related to the use of Glassfish.

It's very sorry but while trying to get a PDF with cyrillic we get in logsMay 18, 2013 1:25:42 AM org.apache.fop.events.LoggingEventListener processEventWARNING: Glyph "с" (0x441, afii10083) not available in font "Courier".

Thank you all for your feedback. There were several questions regarding special characters, or non-English characters. We have a new version of the APEX Listener available (vesion 2.0.2), which addresses some of these issues, i.e. German Umlauts and other special character should not be supported. The latest version can be downloaded here:

In order to support Cyrillic fonts, Turkish fonts, or other fonts that were asked about above, it is necessary to install these fonts on the machine running the Listener and to make them available to the Listener.

I have a question about converting to the APEX Listener FOP. Can we reuse our existing RTF files created with Microsoft Word using the Oracle BI Publisher add-in? In the second video, it says that either XSLT or RTF files can be uploaded, so I'm hoping we can reuse the RTF files we already have.

Hello Marc,Thanks for this article....I've been trying and testing in combination with Stylevision, and so far it looks like this is exactly what I need for a new project.I need to resolve one last requirement: with help from your blog I'm able to generate files we need and also the ability to send them straight to the printer.However, I also need each page of the pdf as a separate file to store this in our CMS.Is there a way to intercept or to retrieve the generated pdf-file(s)?

I am not seeing a PDF print link with Interactive Reports. Followed your direction with Classic reports and that works. Attributes on the region print attributes are different for interactive than classic. Does interactive pdf printing work with Apex Listener 2? If so, what are the attributes and values that need to be set ? The print attributes page does not present the same options.

Hi Marc, I was able to generate pdf file as mentioned in your tutorial, but when I change the 'Output Format' to Excel or Word I am not getting proper output.Can we use the same XSLT report layout for downloading in Excel or Word format?

In order to get Excel or Word output, you will need to configure Oracle BI Publisher as your print rendering engine. This requires a BI Publisher license. With the built-in FOP or APEX Listener based configuration you only get PDF. You can however export any report in CSV format and open this in Excel.

Hello Marc. As I've learn to use Apex as a hobby and start developing Apex apps just for my fun, i was wondering about how to install 4.2.2 patch without having a support contract with Oracle to be able to download just the patch (not reinstalling Apex).

Oracle is only making full installs available for download via OTN. Patches need to be downloaded through Oracle Support. So unless you have an Oracle Support contract, you will need to re-install APEX in order to get to the latest patch release and then re-import your apps.

Thanks for the demo!I have installed OC4J and deployed FOP...It's standalone OC4J, so I used localhost:8888/fop in the printer server configuration.......but nothing happens....any clue where to start to dig? my configuration is correct?

To get non-English characters to work, different fonts need to be configured then the ones bundled in. I have not tested this yet myself, so I don't have any information on how to do this at this time.

There could be any number of reasons for the XML not being valid. I would stick to a very simple example, try to get that to work, and then go from there. Also, I have not tried this with Tomcat, so I suggest you run the APEX Listener stand-alone first. The APEX Listener log also provides some error messages, when there's something wrong with your XSL

Hi Rob - APEX support both XSLT and RTF templates, however the APEX Listener only support XSLT, so if you want to use RTF templates, then you will need to configure Oracle BI Publisher as your backend print server.

Hello Marc, this tutorial was of great help to me and I used it to develop an application we use to create a large number of documents on a daily basis, however, there's one more thing I need and I hope you can help me out.

For the application I created a report-query together with a xsl-fo report-layout created in Altova Stylevision.I've enabled and configured print-options (APEX Listener) and when I press my application button, I'm perfectly capable of viewing and/or saving the pdf-version of my report.

Additionally I want to save my report as BLOB in the database, but when I try this using apex_util.get_print_document, all I get in my table is a html-page containing the message "503-service unavailable".

Meanwhile I found out that Apex Listener is doing all of the PDF-renedering and is therefore not being sent back to the database.I really do need the pdf going to the database, so do you know of a way to achieve this?

Any help will be greatly appreciated!Marco de HaanEtten-Leur Netherlands

If you want to store the PDF as a BLOB in the database, you will need to make sure that your APEX Listener is configured in such a way, that it accepts PDF rendering request directly from the database. Take a look at our whitepaper on "Creating Custom PDF Reports with Application Express and the APEX Listener" here:

Allthough I had already added the entry you mentioned to the defaults.xml, I haven't been able to get this to work.

I had my Apex Listener installed in GlassFish, so I decided to try this with the standalone listener first, but without succes so far.

When I run the procedure "apex_util.get_print_document" in my APEX-application, the Apex Listener console first reports an informational message saying "INFO: Character decoding failed. Parameter [#x200B;....".A little further down it says " ​

looks like an issue with some special characters. I suggest you take a look at you XSLT and see if there's anything that needs escaping or can be removed. I had similar issues with Stylevision generated XSLT files, where I had to manually remove some unneeded special characters, especially with the line breaks inserted by that tool. Also, when using the print APIs, you need to ensure you're escaping special characters in your XML data. The built-in print functions do that automatically. But the APIs don't know anything about your content, so you need to do your own escaping. I suggest you try a very simple example first (just a few records with a simple stylesheet) to make sure you overall process flow works, and then iterate to a more complex case.

Hello Marc,Sorry to bother you again, but I tried to follow your suggestion. I created a very simple stylesheet on the EMP-table using StylusStudio. I noticed that the stylesheet looked much more simple than the one generated by StyleVision, so that seemed positive.Again I'm perfectly capable of generating a pdf straight to the browser, but when I want it to be generated by APEX_UTIL.GET_PRINT_DOCUMENT I end up with a "503 - Service unavailable" and the standalone APEX-listener first saying "Feb 18, 2014 10:32:25 AM com.sun.grizzly.util.http.Parameters processParametersINFO: Character decoding failed. Parameter [template] with value[...here the complete xslt-contents...] has been ignored. Note that the name and value quoted here may be corrupted due to the failed decoding. Use debug level logging to see the original, non-corrupted values.java.io.CharConversionException: isHexDigit

I've tried all signatures of the procedure (with stylesheet and data as blobs etc) but with no luck.

Any suggestions?I really need the pdf to be generated in the database for further processing to DMS etc.

HI MarcThank for this video.Running into issue, install latest apex 4.2.4 with listener 2.05 standalone just to try this. But PDF downloaded gives me error "because it is either not a supported file type or because the file has been damaged. " when download it as RTF then it shows "Syntax error in 'node()|@* except @altova-DisableOutputEscaping'." any suggestions.

We have a problem with special characters in our APEX listener. For example the character "&" comes out as %26amp; when the XML is exported. Our database NLS_CHARACTERSET is WE8MSWIN1252. Does the Listener not use this set automaticly? Any idea what we need to configure to get this to work?

Thank you for your excellent blog. It really helped a lot! We are using APEX listener 2.0.5 and our database settings has WE8MSWIN1252 as character set. However the listener seems to have problems with character sets. For example & comes out in the XML as %26amp;. We use APEX 4.2.2Do you have any idea what the cause could be? Or what we can change to fix this?

Hi Joni - hard to tell what could be causing this. Are you using classic reports, report queries, or the print API? Have you tried this on apex.oracle.com, we use UTF-8 there, so at least you would know if it's character set related.

We use a classic report. We configure the print attributes to export the XML and there the special characters come out differently.For example, as you export the XML :http://apex.oracle.com/pls/apex/f?p=59743:2

I can't seem to test it properly on apex.oracle.com because I think this environment is working with BI Publisher and not the APEX listener?

Hi Joni - just to give you an update, there were a few special characters, like ampersand, plus, greater than, less than, that causes issues with the APEX Listener - i.e. while the "old" via of calling BI Publisher or FOP worked just fine, using the APEX Listener required a different type of character escaping. This has now been fixed in our forthcoming APEX 4.2.5 release / patch set. We do not have a release date for this yet though.

Hı Marc,Thank you for the demоs and answers.But I have the problem.I created the rtf-template in Word (PI PUBLISHER plugin). Preview - I see the result. But “Report query – Test Report” report empty.What is the reason? Help us, please.

VLG: this isn't a lot of information to go by, I suggest you try out a very simple RTF first, and get that to work in the Preview and in APEX, and then go from there. Also, please post your questions in our OTN forum:

Hi - could you try this out on a local instance, using APEX 4.2.4 and the APEX Listener. We're using BI Publisher on apex.oracle.com, and for this to work you need an instance that has the APEX Listener configured as the print server.

Hi Marc,How to configure pdf printing in apex 5.0? In instance setting, there ia only oracle rest data service. Is it different with apex listener? I already configure oracle rest data service but still failed. The pdf file can't load. Do you have solution? Please help me

About Me

I’m a Senior Software Development Manager for Oracle Application Express (APEX). Having joined Oracle in 1998, I was first introduced to APEX in 2001 while working on a Consulting project, attempting to write extensions to the Oracle E-Business using various technologies and finally succeeding with APEX.

Sometime after that, I joined the Oracle Application Express development group, and together with my team, we’ve been developing the reporting engine, tabular forms, PDF printing and integration with BI Publisher, Oracle Forms to APEX conversion, charts, dynamic actions and the APEX UI and themes.

One of the best things about APEX over the years has been the fact that it’s easily available via Internet, using simple tools like a web browser. This is what makes it possible for me to live in New York City, while working with my team members in India, Europe and the United States, together, every day, on a single APEX instance – I guess they call that cloud computing these days. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.