BI Publisher: Data Templates - Simplified Error Handling

Debugging data templates and "calculated formulas"/"report triggers" can be tricky. This is especially true if a developer needs to test there queries in sql*plus or concurrent manager (what output utility should be used). Or what about user requests for putting error messages directly into format templates dynamically. Well there is a solution to all of these problems and yes you can have your cake and eat it too.

Now in the past I have talked up a storm about pipelined functions and how useful they are for reporting and not just etl. If you haven't read-up on pipelined functions I suggest you do so, you don't know what your missing out on! (see first article, second article)

Below is the outlined technical approach:

1. Pass in a debug parameter that specifies the debug output ie: DBMS, CONCURRENT, XML
2. In the beforeReportTrigger set the debugging method
3. Call a debugging procedure which is able to determine the right logging mechanism.

Now, I know what your thinking, this is way too simple, it can't be that easy. DBMS and CONCURRENT manager output mechanisms are straight forward but how would XML work. This where the pipelined function comes in. Click on the pic below. Notice what happens when the debug procedure finds that g_debug is set to xml. The debug procedure instead populates a pl/sql table, I wonder what were going to with that!




All one would need to do now is create a pipelined function to get the values out the table. Below is another code snippet.



From there it's a simple one line query to get everything out of the pl/sql table:
SELECT seq, message FROM table(sample_pkg.get_debug_log) Click here to download the sample package pl/sql source code.

A couple of things to keep in mind. Because the debugging is parametrized an ebs developer can configure there concurrent program setup ie: set the default for the p_debug parameter to be "CONCURRENT". This is a really nice/flexible feature. As an example a developer could be running this same code locally in toad (or the BIPublisherIDE) and could change the output to DBMS or what have you with no worries because it's parametrized.....

For the reports6i developers you have to admit this sure beats the socks off of srw.message! So, why fight the feeling, make the switch :-)