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
Create a report with Data Template code for the Data Triggers
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.
View Report. Data is displayed from the staging table populated by 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.
