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:





Posted by at

59 comments:

  1. ayhanApril 28, 2013 at 11:02 AM

    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

    how can I fix this problem

    ReplyDelete
  2. Mathieu MeeuwissenApril 29, 2013 at 5:07 AM

    Great to read about the integration of PDF printing. Makes it much easier to implement.

    I was wondering whether this release of APEX now supports xmltype columns/ xmltype syntax in the defined report queries (shared components)?

    regards,

    Mathieu

    ReplyDelete
  3. Balaji ChellappaApril 29, 2013 at 11:41 AM

    Marc,
    Thank you very much for integrating PDF printing into 4.2.2. Just for this feature alone I will upgrade to 4.2.2.
    Thanks
    Balaji

    ReplyDelete
  4. Andrzej Nowakowski (APEXUTIL.COM / DBE)April 29, 2013 at 4:16 PM

    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 : (

    ReplyDelete
    Replies
    1. AnonymousJune 15, 2013 at 4:44 PM

      Apache FOP printing does support other languages like arabic, you just need the correct TTF files and proper configuration of APACHE FOP. This link can help you:

      http://www.java4less.com/fopdesigner/fodesigner.php?info=guide

      Delete
  5. Marc SewtzApril 29, 2013 at 5:31 PM

    Hi Mathieu,

    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.

    Regards,
    Marc

    ReplyDelete
  6. Marc SewtzApril 29, 2013 at 5:36 PM

    Hi Ayhan,

    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.

    Regards,
    Marc

    ReplyDelete
  7. AnonymousApril 30, 2013 at 9:23 AM

    Hı Marc,
    Thank you for the demo.
    But I have the same problem as Ayhan. When I use Cyrillic characters in data or static labels pdf file does not open or open with ???? (question marks in the text)

    how can I fix this problem



    Thank you in advance

    Regards,
    Stojan Petrov Bulgaria

    ReplyDelete
  8. AnonymousMay 3, 2013 at 2:42 PM

    Hi Marc,
    Thank you for the demo !
    I tested with Stylus Studio and it works very good but
    it 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: e22ee54
    Message: stream must have data"
    (Note: My generated XLS file is 1.0 version as you recommended.)

    Mario Romero
    Insum Solutions.

    ReplyDelete
  9. AnonymousMay 3, 2013 at 2:45 PM

    Hi Marc,
    A brief question: Do you know a free tool to generate the XSL file ?
    Thank you !
    Mario Romero
    Insum Solutions.

    ReplyDelete
  10. AnonymousMay 6, 2013 at 9:52 AM

    Hi Marc,
    Do you know a free tool to generate and customize XSL files ?
    Thank you !
    Mario Romero
    Insum Solutions

    ReplyDelete
  11. Marc SewtzMay 6, 2013 at 11:56 AM

    Mario: I have no specific recommendations for free tools. Might be best to go try some of the products that offer free trials, see which one works best for you.

    ReplyDelete
  12. Marc SewtzMay 6, 2013 at 11:58 AM

    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.

    ReplyDelete
    Replies
    1. AnonymousMay 13, 2013 at 2:11 PM

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

      Delete
  13. UnknownMay 6, 2013 at 12:07 PM

    Unfortunately Glassfish generates an error:

    com.sun.org.apache.xerces.internal.impl.io.MalformedByteSequenceException: Invalid byte 2 of 3-byte UTF-8 sequence.

    ReplyDelete
  14. Marc SewtzMay 9, 2013 at 8:26 AM

    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.

    ReplyDelete
  15. UnknownMay 17, 2013 at 3:38 PM

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

    Any ideas would be very appreciable.

    ReplyDelete
  16. Marc SewtzMay 22, 2013 at 11:08 AM

    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:

    http://www.oracle.com/technetwork/developer-tools/apex-listener/overview/index.html

    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.

    ReplyDelete
    Replies
    1. UnknownJune 14, 2013 at 10:19 PM

      Marc can you be more specific how we can do that, not all of us are java gurus.

      Delete
    2. AnonymousJuly 18, 2013 at 5:20 PM

      What is a procedure to make them available to the Listener?

      Delete
  17. Barry G.June 11, 2013 at 9:00 PM

    Hello, Marc. Thank you for the information. Very useful.

    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.

    ReplyDelete
  18. MarcoJune 12, 2013 at 7:36 AM

    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)?

    Hope you can help!
    Best regards

    ReplyDelete
  19. UnknownJune 25, 2013 at 6:14 PM

    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.

    Thx Mike

    ReplyDelete
  20. AnonymousJuly 4, 2013 at 5:38 AM

    How can we make new fonts (with special characters) available to the Listener?

    I'm running standalone mode on linux server.

    ReplyDelete
  21. SrinivashSeptember 5, 2013 at 12:33 AM

    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?

    Thanks & Regards
    Srinivash

    ReplyDelete
    Replies
    1. Marc SewtzSeptember 11, 2013 at 5:50 PM

      Hi Srinivash,

      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.

      Regards,
      Marc

      Delete
    2. SrinivashSeptember 12, 2013 at 6:45 AM

      Thanks a lot Marc for the information.

      Delete
  22. UnknownSeptember 11, 2013 at 7:13 AM

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

    Any thoughts?
    Thank you.
    Gabriel

    ReplyDelete
    Replies
    1. Marc SewtzSeptember 11, 2013 at 5:48 PM

      Hi Gabriel,

      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.

      Regards,
      Marc

      Delete
  23. PELUDONovember 21, 2013 at 6:15 PM

    Hello Marc,

    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?

    Thanks,
    EFREN

    ReplyDelete
  24. UnknownDecember 22, 2013 at 11:17 AM

    Hi Marc...

    We have used apex listener to print PDF reports successfully, however arabic text is not printing.

    This is the same question related to special characters and non-English characters... is there a patch to enable arabic printing?

    ReplyDelete
    Replies
    1. Marc SewtzJanuary 13, 2014 at 11:27 AM

      Abdullah,

      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.

      Regards,
      Marc

      Delete
  25. UnknownJanuary 13, 2014 at 8:33 AM

    Hallo Marc

    I cant seem to get it to work. I am using tomcat on a win platform.
    The pdf is produced, only it is not valid. I am dont have a defaults.xml, maybe that is a clue ? Do you have any suggetions ?

    regards,

    Frank Wassens
    Groningen Netherlands

    ReplyDelete
  26. Marc SewtzJanuary 13, 2014 at 11:26 AM

    Frank,

    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

    Regards,
    Marc


    ReplyDelete
  27. UnknownJanuary 16, 2014 at 4:02 AM

    Hello Marc,

    A question from one of the earlier posts: "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."

    Can PDF printing use only the XSLT as template or also RTF files?

    Regards,

    Rob van Merle
    Groningen The Netherlands

    ReplyDelete
  28. OrchiRussiaJanuary 16, 2014 at 7:36 AM

    With best wishes from Russia! I am very glad when find your blog. Thank you, thank you very much. I was looking for such solution not one month.
    Thanks for help.

    ReplyDelete
  29. Marc SewtzJanuary 17, 2014 at 3:49 PM

    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.

    ReplyDelete
  30. UnknownJanuary 21, 2014 at 6:30 AM

    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 Haan
    Etten-Leur Netherlands

    APEX 4.2.0.4.00.08
    GlassFish 3.1.2.2 with the Apex Listener 2.0.5
    Oracle 10.2.0.5 database

    ReplyDelete
  31. Marc SewtzJanuary 21, 2014 at 3:06 PM

    Hi Marco,

    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:

    http://www.oracle.com/technetwork/developer-tools/apex/application-express/apex-white-papers-1866964.html

    This paper outlined how you will need to add the following line to the defaults.xml file of the APEX Listener:

    true

    Also, make sure to use APEX 4.2.2 or newer. Looks like you are using APEX 4.2.0, which does not support this setup.

    Regards,
    Marc



    ReplyDelete
  32. Marc SewtzJanuary 21, 2014 at 3:08 PM

    Marco: quick follow up, looks like my XML did not get posted correctly, so in PDF whitepaper, search for "misc.enableOldFOP", that is the attribute you will need to set

    ReplyDelete
    Replies
    1. UnknownJanuary 22, 2014 at 10:04 AM

      Hello Marc,

      Thanks for the quick response.

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


      I don't know how to work around this....do you have a clue?

      Hope you can help me finalizing my application!

      Thanks in advance!

      Best regards,
      Marco

      By the way, my APEX version is 4.2.4.00.08

      Delete
  33. Marc SewtzJanuary 22, 2014 at 12:40 PM

    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.

    ReplyDelete
    Replies
    1. UnknownFebruary 18, 2014 at 4:54 AM

      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 processParameters
      INFO: 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.

      Delete
  34. NeelJanuary 23, 2014 at 10:34 AM

    HI Marc
    Thank 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.

    ReplyDelete
  35. Joni VandenbergheJanuary 28, 2014 at 11:18 AM

    Hi Marc,

    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?

    Regards,
    Joni

    ReplyDelete
  36. Joni VandenbergheJanuary 28, 2014 at 11:21 AM

    Hi Marc,

    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.2
    Do you have any idea what the cause could be? Or what we can change to fix this?

    Kind regards,
    Joni

    ReplyDelete
    Replies
    1. Marc SewtzJanuary 31, 2014 at 1:16 PM

      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.

      Delete
    2. Joni VandenbergheFebruary 11, 2014 at 8:20 AM

      Hi Marc,

      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?

      Regards,
      Joni

      Delete
    3. Marc SewtzFebruary 27, 2014 at 3:49 PM

      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.

      Delete
  37. UnknownJanuary 29, 2014 at 5:28 AM

    Marc, We have a problem with the "File Name". We would like this to be the same als one of the fields on the screen. I have tried &P20_NAAM. and :P20_NAAM but both don’t work.
    Is this a bug?

    ReplyDelete
    Replies
    1. Marc SewtzJanuary 31, 2014 at 1:14 PM

      Hi - unfortunately the file attribute does not current support item substitutions,

      Regards,
      Marc

      Delete
  38. VLGJanuary 29, 2014 at 6:16 AM

    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.

    ReplyDelete
    Replies
    1. Marc SewtzJanuary 31, 2014 at 1:13 PM

      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:

      https://community.oracle.com/community/developer/english/oracle_database/application_express

      This way others can benefit from any answers, and you also have other APEX Team members, as well as APEX community members looking at this and perhaps being able to assist as well.

      Delete
  39. AnonymousJanuary 31, 2014 at 11:07 AM

    Marc, when you have some time please look into this issue posted on Oracle APEX forum. https://community.oracle.com/message/11343580

    I am having trouble getting images to render in the PDF.

    Thanks!
    JMcG

    ReplyDelete
    Replies
    1. Marc SewtzJanuary 31, 2014 at 1:10 PM

      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.

      Regards,
      Marc

      Delete
  40. UnknownJanuary 31, 2014 at 1:09 PM

    do i need weblogic or glassfish server to use oracle apex listener in production ?

    ReplyDelete
  41. Marc SewtzFebruary 18, 2014 at 9:52 AM

    Please take a look at the APEX Listener OTN page for information on supported configurations:
    http://www.oracle.com/technetwork/developer-tools/apex-listener/overview/index.html

    ReplyDelete
  42. UnknownJuly 1, 2015 at 9:04 AM

    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

    ReplyDelete
  43. Marc SewtzJuly 1, 2015 at 9:09 AM

    Oracle Rest Data Services (ORDS) is just the new name for the APEX Listener, i.e. dependent on which version of APEX you're using, you would either pick ORDS or the APEX Listener.

    ReplyDelete

Subscribe to: Post Comments (Atom)