Oracle ApEx and BI Publisher

I'm running a BI Publisher course this week, and one of the areas the client was really interested in was integration of BI Publisher with Oracle ApEx. As the notes on OTN and various blogs are sometimes a bit confusing, I thought I'd do a quick walkthrough of how the integration works. Setting up BI Publisher and ApEx integration consists of two steps; first you have to configure ApEx to work with BI Publisher (and if you're using Oracle 11g as your ApEx database, configure the database to allow outbound network connections), and then you need to generate your report within ApEx and BI Publisher Desktop.

In this example I'm using Oracle BI Publisher 10.1.3.3.2 (as packaged along with OBIEE 10.1.3.3.2) along with Oracle Database 11.1.0.6 and ApEx 3.0. The first step in configuring ApEx is to log in to the Apex Administration application and bring up the Manage Environment Settings > Instance Configuration page, which we'll use to tie in BI Publisher to this ApEx installation.

Selecting this administration page allows us to enter the connection details for the BI Publisher server. Using this dialog, you enter the hostname of the BI Publisher server, the port number and the page details of the BI Publisher interface. Note also that you radio button is set to "Advanced Printing" which signifies that we're going to use BI Publisher; the Standard Support option above it requires you to use an Apache module that also prints to PDF but doesn't have as much functionality as BI Publisher - you'd use this if you want BI Publisher to print to PDF but you haven't licensed BI Publisher.

One more thing that you have to do, if you're using ApEx as part of Oracle 11g, is to configure the database to allow outward network connections, which is disabled by default (on earlier database releases this step isn't needed. To do this, you run a PL/SQL script that uses the DBMS_NETWORK_ACL_ADMIN built in package to administer the database network access control list. This script assumes you're using ApEx 3.0; if you're using 3.1 you'll need to change the references to FLOWS_030000 to FLOWS_030100

DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to '*' and give FLOWS_030000
  -- the "connect" privilege if FLOWS_030000 does not have the privilege yet.
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS

WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
-- Before checking the privilege, make sure that the ACL is valid

-- (for example, does not contain stale references to dropped users).

-- If it does, the following exception will be raised:
-- ORA-44416: Invalid ACL: Unresolved principal 'FLOWS_030000'

-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID

FROM XDB.XDB$ACL A, PATH_VIEW P

WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND

EQUALS_PATH(P.RES, ACL_PATH) = 1;
DBMS_XDBZ.ValidateACL(ACL_ID);

IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'FLOWS_030000',

'connect') IS NULL THEN

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,

'FLOWS_030000', TRUE, 'connect');

END IF;
EXCEPTION

-- When no ACL has been assigned to ''.

WHEN NO_DATA_FOUND THEN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',

'ACL that lets power users to connect to everywhere',

'FLOWS_030000', TRUE, 'connect');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','');

END;

/

COMMIT;

Run this script as the SYS or SYSTEM user, and you should then be ready to go.

To create an ApEx report that uses BI Publisher, log on to a workspace and create a new Shared Component > Report Query.

When the new Report Query dialog comes up, press the Create button to create the query, and then enter the SQL for the query you're going to run. In this case, I'm using a query against the database tables used by the ApEx sample application.

On the next page of the dialog you can test the query - do this and make sure it comes back with some data. Next, you can tie your ApEx parameters to the report, and then a dialog is displayed where you can download the XML for your report and use it, with BI Publisher Desktop, to lay our your report template.

Once you've used BI Publisher Desktop to create your template RTF file, you can use the same dialog to upload the RTF file to ApEx so that it can then send it to BI Publisher when your report is displayed. One thing to note here is that the BI Publisher references only appear once you've done the configuration steps I mentioned beforehand - if you don't do these, ApEx just makes reference to FO and XML files which is how it handles PDF printing when BI Publisher isn't around.

Once you've uploaded the RTF file, ApEx on the next page displays a link that you can use when calling the report from elsewhere in your ApEx application, and you can press the Test Report button to try out the report and check it displays OK.

Pressing the Test Report button brings up a dialog asking if you want to open the PDF document, once you accept this the BI Publisher report is displayed as expected.

So all in all, it's a relatively straightforward process, once you've worked out how to do the initial configuration step and if you're using 11g, how to enable outbound network connections from the database. Thanks to Borkur for working most of this out for our course examples.