Oracle job alert notification

I am running oracle 11g. I try to setup an email notification for a job.

BEGIN
 DBMS_S开发者_如何学JAVACHEDULER.add_job_email_notification (
  job_name         =>  'JOB_COLLECT_SESS_DATA',
  recipients       =>  'kaushik.guha@bmo.com',
  events           =>  'job_failed'/*,
  filter_condition => ':event.error_code=600'*/);
END;
/

And I get:

PLS-00302: component 'ADD_JOB_EMAIL_NOTIFICATION' must be declared

Is it some issue related to privileges?

That function exists in 11.2 but not in 11.1.

You may be able to reproduce that functionality by creating another job that looks at job statuses:

select *
from dba_scheduler_job_run_details
where job_name = 'JOB_COLLECT_SESS_DATA'
    and status = 'FAILED'
    and additional_info like 'ORA-00600%'
order by log_id desc;

That query works at least for some errors. But ORA-00600 errors are always special and may not always show up in that table for all I know. You'll want to test this carefully.

I set up a perl script which runs periodically as a cron job, which prints warnings when errors in the DBMS-jobs have occured. Because my crontab has a MAILTO=<email@domain.com> setting, all warnings are going to be sent to me by email.

my $dbh  = ... # set up a database connection using DBI
my $jobs = $dbh->selectall_arrayref("SELECT * FROM USER_JOBS", { Slice=>{}});

for my $job ( @$jobs ) {
    if ($job->{NEXT_DATE} eq '01-JAN-4000') {
        warn "DBMS-Job $job->{WHAT} is currently stopped.\n";
        warn "Last running at: $job->{LAST_DATE} $job->{LAST_SEC}\n";
    }
    elsif ( $job->{FAILURES} ) {
        warn "DBMS-Job $job->{WHAT} has failures.\n";
        warn "Last running at: $job->{LAST_DATE} $job->{LAST_SEC}\n";
    }
    else {
        warn "DBMS-Job $job->{WHAT} is broken.\n";
        warn "Last running at: $job->{LAST_DATE} $job->{LAST_SEC}\n";
    }
}