Job Notification Package

One interesting features of the Oracle Scheduler is the job notification package.

The job notification package can be downloaded here:Oracle Scheduler

It is very easy to install, simply run the job_notification.sql script and provide the outgoing e-mail SMTP server and port number.

In order to get notified about a job’s completion, I ran the following command:

EXEC add_job_email_notification(‘RUN_SERCICE_120DAYS_JOB’,’my_adress@myspace.com’
,’JOB_SUCCEEDED’);

Unfortunately, this command returned an error, stating that the job name was too long for the character buffer:

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at “SYS.ADD_JOB_EMAIL_NOTIFICATION”, line 248

Since the job name is already in place and used accross all of the environments involved, I wanted to keep the job name as is. In order to do so, the solution is the following:

Simply increase the size of the “notifier_job_name” variable from VARCHAR2(30) to VARCHAR2(60) in the “add_job_email_notification” procedure. That simple change did the trick!

There is an OTN Thread about this same issue.