3 Replies Latest reply on Mar 20, 2014 8:20 PM by GregV

    Add Email Job Notification via SQL Developer




      I'm a novice in the Oracle Scheduler space and I have what is likely a novice question.  However, I'm thoroughly turned around after searching and would gladly welcome some help.


      I'm using SQLdeveloper to utilize Oracle Scheduler within my schema.


      A procedure has been successfully created.  The job which executes the procedure is also successfully configured.


      Next I'd like to configure email notification for certain events.


      I've found a number of posts regarding the ADD_JOB_EMAIL_NOTIFICATION procedure.

      I.e., http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm#ARPLS72277


      However, I'm having trouble identifying how to get started implementing this within SQL Developer.


      Can someone please help with how the ADD_JOB_EMAIL_NOTIFICATION can be implemented within SQL developer?


      Another point I want to mention: in a previous prototype, I found a wizard to configure the email notification.  I'm not seeing that now.  Is there a wizard with SQL Developer in which this information can be configured? 


      Extremely grateful for any help you can provide.


      Thanks - Fritz

        • 1. Re: Add Email Job Notification via SQL Developer




          It looks like the email notification is not listed in the SQL Developer's browser.

          Anyway, it's failry easy to use the ADD_JOB_EMAIL_NOTIFICATION procedure.


          First, you need to setup a mail server:


            DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'your_mail_server_address');




          Second, just add a notification on some events for your job:


          DBMS_SCHEDULER.add_job_email_notification (

            job_name   =>  'your_job',

            recipients =>  'your_email@your_company.com',

            events     =>  'job_started, job_completed');




          That's all!

          You can specify a comma-separated list for the recipients and for the events.

          In the previous example, you'll receive a mail at the your_email@your_company.com address when the job starts and when the job completes.

          It will use a default subject and body for the mail, but you can change it through the subject and body parameters.

          1 person found this helpful
          • 2. Re: Add Email Job Notification via SQL Developer

            Thank you for your response.


            I'm entering that block into a SQL worksheet and it has now been accepted.  I was mis-interpreting an error message which was thrown in my earlier attempts.  Looks like the name of my job was problematic.  I've renamed that and the add job email notification block is now being completed.


            Thanks much for your help.


            Also I've tried using the get_scheduler_attribute to see if an email server is already configured.  However, I'm not using this correctly either.  Only these attributes can be viewed through get scheduler attribute.

            Scheduler AttributeDescription


            Default time zone used by the Scheduler for repeat intervals and windows


            Retention period in days for job and window logs


            Maximum number of job slave processes that the Scheduler can start. May be NULL.


            Name of the currently open window


            Time in seconds before an event generated by the Scheduler and enqueued onto the Scheduler event queue expires. May be NULL.


            Any suggestions of how to view which notifications are configured for a job?


            Thanks again! Fritz

            • 3. Re: Add Email Job Notification via SQL Developer



              You can query the ALL_SCHEDULER_GLOBAL_ATTRIBUTE to see if the mail server is configured.