Generate PDF / XLS reports in APEX

Problem description

If you have any complex application in APEX you probably need to generate some untrivial reports and may be in several formats (HTML / PDF / XLSX). It's very painfull to try to do it in pure PLSQL and very expensive to use such enterprise software as Oracle BI Publisher. At the other hand you can use any open report server system like JasperReports but here you have big problem with integration  external servers with APEX security system to allow access reports only for authorized users. It's good enougth to solve this if you have tons of regular changing reports so you can spend much time for enviroment and less time for reports support in the future but what can you do otherwise?

Possible solution

Obviously that any complex report can be generated at backend with any high level programing language like Java, Python, Ruby e.t.c. But how to quick and accurate integrate such report script into an APEX application? Here you can find the way that successfully works for me. Below external PDF report generator integration example. 

So solution consists of three main steps:

1. Add URL/Button for start report generating routine that ends with browser prompt for download report file
2. Run command line script with report generation
3. Generate correct MIME header to allow client browser to download report file

Lets describe them in order of implementation (2,3,1).

Run OS command line script from Oracle

Oracle have not easy way to run OS command line script. You can easy find some workarounds. Here I provide dbms_scheduler method that works well for commercial and for XE editions. (Please note that you must create my-temp-script.sh script file in the /tmp/ dir for use this example) Source code on github

Generate MIME header

1. Create blank page in APEX with blank HTML region
2. Add "on load: before header" branch with following code. (don't forget to create EXPORT_DIR directory in Oracle DB like in previous code sample) Source code on github
   

Add button for starting all this stuff

Nothing to describe here. Just create button on report page that redirect to "MIME header" page from previous paragraph.

That's all folks!