You are here: BI Publisher PL/SQL triggers within BI Publisher Data Template

PL/SQL triggers within BI Publisher Data Template

Written by  Raj
Rate this item
(7 votes)

 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

DYNAMIC_WHERE_CLAUSE varchar2(3200);
P_DEPTNO varchar2(10);
P_SAL number;
P_HIRE_DATE date;
function BeforeReportTrigger return boolean;
function DeptGroupFilter (p_dept_number number ) return boolean;
function AfterReportTrigger (totalOrgsal number) return boolean;
END;
/
CREATE OR REPLACE
PACKAGE BODY emp_test1 AS
function BeforeReportTrigger return boolean IS
begin
IF (P_DEPTNO ='10') THEN
DYNAMIC_WHERE_CLAUSE :='D.DEPTNO =10';
ELSIF (P_DEPTNO ='20') THEN
DYNAMIC_WHERE_CLAUSE :='D.DEPTNO =20';
ELSIF (P_DEPTNO ='30') THEN
DYNAMIC_WHERE_CLAUSE :='D.DEPTNO =30';
ELSIF (P_DEPTNO ='40') THEN
DYNAMIC_WHERE_CLAUSE :='D.DEPTNO =40';
ELSE
DYNAMIC_WHERE_CLAUSE :='1=1';
END IF;
return true;
end;
FUNCTION AfterReportTrigger (totalOrgsal number) return boolean IS
BEGIN
 
INSERT INTO org_sal (report_date,total_sal_amount ) VALUES (sysdate,totalOrgsal);
commit;
return true;
END;
 
FUNCTION DeptGroupFilter (p_dept_number number) return boolean is
BEGIN
IF (p_dept_number='30') THEN
RETURN FALSE;
END IF;
RETURN TRUE;
end;
 
END;
/

PL/SQL logic should be implemented as single PL/SQL Package with following conditions.

  1. All the parameters define in Parameter section of Data Template should be define as parameters in plsql package.
  2. PL/SQL logic should be implemented as function and must return a Boolean value.
  3. 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

-<dataTrigger name="afterReport" source="emp_test1.AfterReportTrigger(:ORG_TOTAL_SALARY)" /> 
 -<group name="G_DEPT" source="Q1" groupFilter="emp_test1.DeptGroupFilter(:DEPT_NUMBER)">
-<dataTrigger name="beforeReport" source="emp_test1.BeforeReportTrigger" />

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.

              SELECT d.DEPTNO,d.DNAME,d.LOC,
              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.

Thanks
Raj.

Read 79679 times

2 comments

  • Comment Link Jitendra Friday, 01 March 2013 09:12 posted by Jitendra

    Hi Raj,

    we have a XML Publisher report.
    there we have used GroupFilter="false" to hide the values of Tags under the group.
    but it is stopping the caculation for tha tag under this group and showing as 0 or null.

    but when i changed the GroupFilter="True" the report showing the expected values for its Tag under the group.

    for example :

    for Group "CALCULATIONS" : the values of Tag "TOTAL_AMOUNT_RAW" is being printed as '0.35' when groupFilter="true"

    -




    but values of Tag "TOTAL_AMOUNT_RAW" i s not printed as 0 when groupFilter="False"
    -




    Please suggest if any clue on this.

    Thanks & regards,
    Jitendra P Patel.

    Report
  • Comment Link Ajay Juriasinghani Friday, 07 September 2012 13:46 posted by Ajay Juriasinghani

    Hi
    How exactly the trigger needs to be called?
    In DB we should create package,package body and trigger part?
    And how to include the same under Oracle DB Default Package ??
    Please reply ASAP..

    Thanks in advance.

    Report

Leave a comment

Make sure you enter the (*) required information where indicated.
Basic HTML code is allowed.