This article is in continuation to my previous article, XML generation using BI Publisher Data Template.
PL/SQL support within Data Template separate the data extraction logic and XML generation in two separate layers. We can hook the PL/SQL logic to implement the Business rules, Data extraction or post processing logic. Through PL/SQL logic, we can push some of the complex data calculation and network intensive operations to Database layer.
Let's discuss following sample Data Template. The data template has associated default plsql package.
Default PL/SQL Package (emp_test1.pls)
CREATE OR REPLACE package emp_test1 as
PL/SQL logic should be implemented as single PL/SQL Package with following conditions.
- All the parameters define in Parameter section of Data Template should be define as parameters in plsql package.
- PL/SQL logic should be implemented as function and must return a Boolean value.
- The package should be define as defaultPackage attribute in Data Template Header.
In the above example, we see following three instance of PL/SQL call. Two dataTriggers and one Group Filter
1) Before Report Trigger
The position of first dataTrigger is before the dataStructure section in Data Template. It gets fire before the very first SQL query executes. In this example, PL/SQL function BeforeReportTrigger drive the DYNAMIC_WHERE_CLAUSE variable based on P_DEPTNO value. P_DEPTNO value will be available within plsql package as the process set the value from data template parameters to pl/sql parameters. That is the reason; we need to define the same set of parameters in PL/SQL as defined in data template. BeforeReportTrigger function set the DYNAMIC_WHERE_CLAUSE parameter calue as “D.DEPTNO =10”. Before executing the SQL query Q1, process replace the substitute variable DYNAMIC_WHERE_CLAUSE with the actual values and following query get fired.
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM from DEPT D, EMP E where D.DEPTNO=E.DEPTNO
and D.DEPTNO =10
PL/SQL parameter/s can be reference as substitute variables within SQL query and this feature allow us to design the dynamic SQL query based on the input parameters.
The before report triggers can be used to designing dynamic SQL queries or filter conditions using substitute variables,populating the temporary or global temporary tables for Oracle apps for complex data extraction process and then write the SQL query against these temp tables.
2) After Report Trigger
The position of “afterReport” data trigger is after the dataStructure section and termed as after Report Trigger. These triggers get fire after the XML generation complete. In our example function “AfterReportTrigger” accept the ORG_TOTAL_SALARY as input parameter and insert a record to ORG_SAL table.
The purpose of after report triggers is to implement any post process logic as cleaning or deleting the temp tables or records, auditing activities.
3) Group Filter
The use of Group filter is to filter out the groups from the XML ouput, if the specified condition implemented in calling function does not match. If the PL/SQL function returns the false, the particular instance of group will not be the part of XML output.
In the example, DeptGroupFilter accept the dept_number element value as input. If the value is 30, it returns false and Group belongs to deptn0=30 will not be there in final xml.
I tried multiple before and aft er data triggers within the same data template and they executed sequentially, So I think we can use multiple before and after dataTriggers. All these three call are simple PL/SQL functions and designed for some specific operations but there is no restrictions on nature of PL/SQL logic inside the function, just make sure it return the appropriate Boolean value.
Please feel free to ask any question on this topic and I will be happy to help you out.