Before and After Report Triggers using Data Templates in Oracle BI Publisher
Here is an example of implementing Data Triggers (Event Triggers) using Data Templates in Oracle BI Publisher (XML Publisher).
As per Oracle documentation, “Data triggers execute PL/SQL functions at specific times during the execution and generation of XML output. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as perform initialization tasks and access the database.”
Data trigger is implemented using the <dataTrigger> element in a data template. This element has two attributes: a name and a source. The source attribute points to a function where our PL/SQL code resides.
Note: It is important that the PL/SQL code called by data triggers should reside in a function and the function should be wrapped in a package. The function should return a boolean value (true for success and false for failure). I tried implementing data triggers using procedure and code that was not wrapped in a package – it did not work for me. So I am only using functions returning a boolean value for data triggers.
Also, location of the trigger indicate at what point the trigger fires. Placing the data trigger element before the <dataStructure> element will fire the code before the data query is executed – this will act like a “Before Report Trigger”. Placing the data trigger element after the <dataStructure> element will execute the code after the data query is executed – acting like a “After Report Trigger”.
In the following example I use the data template to populate a staging table using a “before report” trigger. Then execute a SQL on the staging table to fetch the report data and in the end execute an “after report” trigger to log an entry into a Log table.
For this example, I am using the customers table in the oracle demo database “oe” schema.
Step 1: Create a staging table and a log table. The staging table will be populated by the before trigger and I will make an entry in the log table using the after trigger.
DROP TABLE dataTemplateLog / DROP TABLE stageCustomers / CREATE TABLE dataTemplateLog (actionReport VARCHAR2(100), dtReport DATE) / CREATE TABLE stageCustomers AS SELECT * FROM customers WHERE 1 = 2 /
Step 2: Create a package containing PL/SQL code for the before and after data triggers.
CREATE OR REPLACE PACKAGE dataTemplateTriggers AS FUNCTION beforeTriggerPopulateStage RETURN BOOLEAN; -- Function for Before Report Trigger FUNCTION afterTriggerLogRun RETURN BOOLEAN; -- Function for After Report Trigger END; / CREATE OR REPLACE PACKAGE BODY dataTemplateTriggers AS FUNCTION beforeTriggerPopulateStage RETURN BOOLEAN AS BEGIN DELETE FROM stageCustomers; -- Clear the staging table INSERT INTO stageCustomers SELECT * FROM customers; -- Insert data into the staging table for the report COMMIT; RETURN true; EXCEPTION WHEN OTHERS THEN RETURN false; END; FUNCTION afterTriggerLogRun RETURN BOOLEAN AS BEGIN -- Create a Log entry after the report runs INSERT INTO dataTemplateLog (actionReport, dtReport) VALUES ('Data trigger executed.', sysdate); COMMIT; RETURN true; EXCEPTION WHEN OTHERS THEN RETURN false; END; END; /
Step 3: Create a data template for the Customer report
Here is the data template code:
<dataTemplate name="CustomerReport" dataSourceRef="oe" defaultPackage="dataTemplateTriggers"> <dataQuery> <sqlStatement name="CUSTOMER1"> SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_EMAIL, CREDIT_LIMIT FROM stageCustomers </sqlStatement> </dataQuery> <dataTrigger name="beforeReport" source="dataTemplateTriggers.beforeTriggerPopulateStage()"/> <dataStructure> <group name="G_CUSTOMER" source="CUSTOMER1"> <element name="CUST_FIRST_NAME" value="CUST_FIRST_NAME"/> <element name="CUST_LAST_NAME" value="CUST_LAST_NAME"/> <element name="CUST_EMAIL" value="CUST_EMAIL"/> <element name="CREDIT_LIMIT" value="CREDIT_LIMIT"/> </group> </dataStructure> <dataTrigger name="afterReport" source="dataTemplateTriggers.afterTriggerLogRun()"/> </dataTemplate>
Step 4: Execute the report. The data in the report is from the staging table populated by the Before Report Data Trigger.
Step 5: Select information from the Log table. The latest entry was made by the After Report Data Trigger.
SQL> SELECT substr(actionreport, 1, 30), dtreport FROM datatemplatelog; SUBSTR(ACTIONREPORT,1,30) DTREPORT ------------------------------ --------- Data trigger executed. 30-OCT-09
You can use this example and enhance it to match your requirements for Before and After report triggers in Oracle BI Publisher.