OBIEE - Executing Stored Procedures/Functions before Reports – Before Report Triggers and Global Temporary Tables

Basically the idea is to execute a stored procedure just before running a report. This stored procedure would basically populate the temporary tables and the report would be rendered based on this data. In our case, we shall start with a simple example. We will be creating a Global Temporary table(GTT) which would be an exact copy of the EMP table of scott schema. We shall be creating a function to load the data from the EMP table to this GTT. And this GTT can retain the data for the session using PRESERVE ROWS clause. So, lets first start with a GTT as shown below

CREATE GLOBAL TEMPORARY TABLE EMP_GTT
(
EMPNO NUMBER,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
ON COMMIT PRESERVE ROWS

As you see above, it has the same columns structure as the EMP table. Now lets create a simple function which will insert data from the EMP table to the above GTT table(remember this has no error handling, this is primarily for test purposes).

CREATE OR REPLACE FUNCTION LOAD_GTT RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO EMP_GTT(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
(
SELECT
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
FROM EMP);

COMMIT;

RETURN ‘TRUE’;
END;

      

The PRAGMA AUTONOMOUS_TRANSACTION allows us to do DML operations within the function. Once this is done, go to BI EE Administration tool and import this GTT table. Also design the BM and Presentation Layer.

      

Now, double click on the Connection Pool and go to the Connection Scripts tab. In the Connection Scripts tab -> Execute before query, enter the below query

select load_gtt() from dual

      

Now, save this and go to the Answers and create a simple report. You would notice that the GTT would now have data since the function is executed first and then the report is rendered.

      

Also, if you check the logs, you would notice that the select statement that we included in the connection scripts tab would also have been fired. Do not worry about the order of appearance of the select statement in the logs. If you have any existing stored procedures then just create a function and call that procedure from this function.