CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Victor
Victor

Reputation: 17107

Oracle job alert notification

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

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name         =>  'JOB_COLLECT_SESS_DATA',
  recipients       =>  '[email protected]',
  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?

Upvotes: 2

Views: 1835

Answers (2)

Swen Vermeul
Swen Vermeul

Reputation: 104

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 protected]> 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";
    }
}

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36922

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.

Upvotes: 1

Related Questions