Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Executing Stored Procedures/Functions before Reports – Before Report Triggers and Global Temporary Tables

Posted by Venkatakrishnan J on February 20, 2008

All users who have used BI Publisher or Oracle Reports would be aware of the fact that you can execute stored procedures as before report triggers and after report triggers. Lets look at a similar feature in BI EE today. Though i have discussed about this earlier in the VPD related blog entry, i just thought this in itself deserves a seperate blog entry since a lot of users seem to have this same question. The scenario for today is as shown below,

      

So, 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.

      

About these ads

8 Responses to “Oracle BI EE 10.1.3.3/2 – Executing Stored Procedures/Functions before Reports – Before Report Triggers and Global Temporary Tables”

  1. Vivek said

    Hi Venkat,
    Say, I have another report which needs different GTT to be populated. Can I call diffent function without executing others?

    Thanks,
    Vivek.

  2. Sanjay said

    Is it possible to pass parameters from the stored procedure to obiee ?

  3. Ashok said

    Hi Venkat,

    What I understand is, this way the function gets executed for all the reports. What if I want to call the function only for a specific report? Is there a way to specify that? Please let me know.

    Thanks,
    Ashok.

  4. Vijay said

    Hi venkat,

    My requirement is a kind of same, but i need to pass a parameter to the function(period parameter say for eg: sep-2008) to perform some calculation.
    how to pass a parameter to Obiee which is not a column value from a table?

    Would appreciate your reply.
    Thanks
    Vijay

  5. pramodpandhare said

    useful notes….
    thanku……

  6. san said

    Hi venkat

    The notes is very usefull thanks,

    I need to know if i can call a stored procedure wile runnning the report i.e.,

    Based on some values if i click a button i need to select the data by calling a procedure/function for the given values on the dashboard and show the data given back by procedure/ function in the dashboard.

    Thanks
    san

  7. Shyam Rao said

    We have a need to set a database session var for every user.
    We need to set NLS_LANG for everu user/session.
    With connection pooling, it is not possible to do it per session but needs to be done per query.
    So if we are indeed able to achieve this using Execute Before Query – how will OBIEE caching now behave? Wont this become an issue?
    thanks.

  8. Sara said

    Hi,
    I’m reading your post.

    I have the same need. I need to run a store procedure via Answer (or Publisher), via Direct Database request. Is it possible? I read in metalink that you can not do it.

    Bye
    Sara

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: