Rather than have the business inform us their reports our not right I was tasked with creating an alert if the jobs did not run. Usually we set up alerts for things that break or actually completed. I think this is the first time I've had to build something to the opposite.
Here's what I came up with:
CREATE OR REPLACEYou can find it here in my Google Code home for DBA Utilities. I do believe that at some future point this will be incorporated into a package, but for now it is a standalone procedure.
b VARCHAR2(3) := CHR(10);
TYPE r_table IS RECORD
TYPE t_table IS TABLE OF R_TABLE;
l_table T_TABLE := T_TABLE();
l_subject VARCHAR2(50) := 'Alert - Jobs have not run';
SELECT job_name, next_run_date, job_action
BULK COLLECT INTO l_table
WHERE state = 'SCHEDULED'
AND enabled = 'TRUE'
AND next_run_date < SYSDATE;
l_message := 'The following jobs have not run today:' || b;
FOR i IN 1..l_table.COUNT LOOP
l_message := l_message || 'Job: ' ||
l_table(i).job_name || b;
l_message := l_message || 'Next Run Date: ' ||
TO_CHAR( l_table(i).next_run_date, 'MM/DD/YYYY HH24:MI:SS' ) || b;
l_message := l_message || 'Action: ' ||
l_table(i).job_action || b;
( sender => '
recipients => '
subject => l_subject,
message => l_message );
You'll receive a nice little email noting the JOB_NAME, scheduled NEXT_RUN_DATE and the JOB_ACTION (the anonymous block).