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