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.
Posts
