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

	CREATE DIRECTORY TMP AS '/tmp/';
	create or replace
	PROCEDURE system_run(cmd IN varchar2)
	IS
	script_file varchar2(40) := 'my-temp-script.sh';
	script_data varchar2(4000);
	MyFile utl_file.file_type;
	d varchar2(4000);
	dump_file varchar2(40) := 'my-temp-file.dat';
	dump_type utl_file.file_type;
	BEGIN
	-- Open file
	MyFile := utl_file.fopen('TMP',script_file,'w');
	-- Write data to file
	script_data := '#!/bin/bash' || chr(10) || cmd||'>'||'/tmp/'||dump_file;
	utl_file.put_line(MyFile, script_data, FALSE);
	-- Close file
	utl_file.fflush(MyFile);
	utl_file.fclose(MyFile);
	-- Purge old logs, no fun anyway
	dbms_scheduler.purge_log(JOB_NAME=>'TEST_RUN');
	-- Execute script
	-- The job is created as disabled as
	-- we execute it manually and will
	-- drop itself once executed.
	dbms_scheduler.create_job(
	job_name => 'TEST_RUN',
	job_type => 'EXECUTABLE',
	job_action => '/bin/bash',
	number_of_arguments => 1,
	start_date => SYSTIMESTAMP,
	enabled => FALSE);
	dbms_scheduler.set_job_argument_value('TEST_RUN', 1, '/tmp/'||script_file);
	dbms_scheduler.enable('TEST_RUN');
	-- Wait for the job to be executed
	-- usually done within 1 second but
	-- I set it to 2 just in case.
	dbms_lock.sleep(2);
	-- Open the output file and
	-- print the result.
	dump_type := utl_file.fopen('TMP',dump_file,'r');
	loop
	begin
	utl_file.get_line(dump_type,d);
	dbms_output.put_line(d);
	exception
	when others then
	exit;
	end;
	end loop;
	utl_file.fclose(dump_type);
	-- Clean up our temp files
	utl_file.fremove('TMP', script_file);
	utl_file.fremove('TMP', dump_file);
	END;

view raw oracle_system_run hosted with ❤ by 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
   

   	DECLARE
   	l_filename BFILE;
   	v_export_dir VARCHAR2(2000);
   	v_length integer;
   	l_blob blob;
   	des_offset number := 1;
   	src_offset number := 1;
   	x number;
   	BEGIN
   	system_run('/path/to/report/generation/script.sh -r '); /*Run report generator script*/
   	l_filename := bfilename('EXPORT_DIR','/expected_report_file_name.pdf'); /*Please create EXPORT_DIR in Oracle before use*/
   	v_length := dbms_lob.getlength(l_filename);
   	DBMS_LOB.createtemporary(l_blob, FALSE);
   	dbms_lob.open(l_filename, dbms_lob.lob_readonly);
   	DBMS_LOB.LOADBLOBFROMFILE(dest_lob=>l_blob,
   	src_bfile=>l_filename,
   	amount=>v_length,
   	dest_offset=>des_offset,
   	src_offset=>src_offset);
   	owa_util.mime_header('application/pdf', FALSE );
   	-- set the size so the browser knows how much to download
   	htp.p('Content-length: ' || v_length);
   	-- the filename will be used by the browser if the users does a save as
   	htp.p('Content-Disposition: attachment; filename="expected_report_file_name.pdf"');
   	-- close the headers
   	owa_util.http_header_close;
   	-- download the BLOB
   	wpg_docload.download_file(l_blob);
   	DBMS_LOB.freetemporary(l_blob);
   	EXCEPTION WHEN OTHERS THEN
   	DBMS_LOB.freetemporary(l_blob);
   	RAISE;
   	END;

   view raw oracle_create_mime hosted with ❤ by 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!