Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Executing iBots from Dashboards/Answers – Sending Emails to Administrators

Posted by Venkatakrishnan J on March 17, 2008

In my previous blog entry here, i had shown you how to go about calling command line utilities(for Purging the Cache) from Dashboards/Answers. Along the same lines, lets look at another possible use case of the procedure that was used there today. Today, we shall see how to go about calling the ibots from Dashboards/Answers. i.e our aim is to execute the ibots directly from Dashboards by clicking a single URL. This would be useful in a lot of situations wherein you would like to send emails to Administrators whenever the database server is down or when there is some error in some of the reports. In order to achieve this, the first step is to create a simple ibot which would basically send out an email to the Administrator. Also, this ibot would contain a report (generally a diagnostics report). In our case, lets include a simple report.

      

      

Once this is done, find out the jobid of this ibot from the Job Manager console.

      

Now, create a batch file to call this ibot on an on-demand basis(on unix you would have to create a shell script). This batch file would basically have this command.

saschinvoke -u Administrator/Administrator -j 1

      

The advantage of the above batch file is that you can also pass parameters (jobids etc) dynamically to it. The next step is to create a simple DBMS_SCHEDULER job to call this utility.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘saschinvoke1’,
job_type => ‘EXECUTABLE’,
job_action => ‘D:\Oracle\OracleBI\server\Bin\saschinvoke.bat’,
repeat_interval => ‘FREQ=YEARLY’,
enabled => TRUE
);
END;

Now, create another procedure to run this job.

CREATE OR REPLACE PROCEDURE PRO_INVOKEIBOTS IS
BEGIN
DBMS_SCHEDULER.RUN_JOB(‘saschinvoke1’);
HTP.PRINT(‘<html>’);
HTP.PRINT(‘<head>’);
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>EMAIL Successfully Sent</title>’);
HTP.PRINT(‘</head>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>Email Successfully Sent</h1>’);
HTP.PRINT(‘</body>’);
HTP.PRINT(‘</html>’);
END;

This is the procedure which we shall expose to be called from a URL using the Pl/SQL gateway. In order to do that, grant execute privilege on the above procedure to ANONYMOUS and add the procedure to the list of accepted and executable packages by the pl/sql gateway.

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)
return boolean is
begin
if upper(procedure_name) in (‘HR.PRO_INVOKEIBOTS’) then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

GRANT EXECUTE ON PRO_INVOKEIBOTS to ANONYMOUS;

Once this is done, create a URL link in the dashboard to call the below URL

http://localhost:7779/apex/hr.pro_invokeibots

      

Clicking on the above URL should automatically send an email to the Administrator.

      

Advertisements

5 Responses to “Oracle BI EE 10.1.3.3/2 – Executing iBots from Dashboards/Answers – Sending Emails to Administrators”

  1. Nice Blog. I like the layout you used. Did you make that yourself?

    – Randy Nichols.

  2. navnit said

    It is a good article.

    Do you have any document for Integrating ESSBASE with OBIEE
    and Ebusiness suite with OBIEE.What should be taken care when doing it.

    whe defining the OBIEE environment for OBIEE for ESSBASE and Ebusiness suite
    are there any guidlines for sizing the CPU,RAM, Dataware house, Harddisk

    Thanks in advance

  3. DamionKutaeff said

    Hello everybody, my name is Damion, and I’m glad to join your conmunity,
    and wish to assit as far as possible.

  4. Anuradha said

    Hi,
    We tried above implementation.
    We dont have Oracle DB installed on local machine.
    We are connecting to Oracle DB hosted at (host name: rws60008rems.us.oracle.com, port: 1587)(oracle 10g).

    Please let us know which service we need to look for, and should be started, for accessing pl/sql gateway. Then that service port we can use in the url to call the pl/sql procedure, the way you have used port:7779.

    Thanks!

  5. adam said

    hi

    I am using this functionality to execute a process flow that refreshes datamart. The problem is the url is public/anonymous. How can i make it secure so a login would be required without using an apex frontend (e.g is it possible just to prompt for a username/password like what occurs on xmldb). We would like it seemless for the user like they are not using another application.

    I have a link on a dashboard which an end user admin for the subject area has access to. However all he has to do is copy the url and send it around for it to be available to all.

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

 
%d bloggers like this: