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

    Add Email Job Notification via SQL Developer

    Fritz-OC

      Hello,

       

      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
          GregV

          Hi,

           

           

          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:

          BEGIN

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

          END;

          /       

           

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

          BEGIN

          DBMS_SCHEDULER.add_job_email_notification (

            job_name   =>  'your_job',

            recipients =>  'your_email@your_company.com',

            events     =>  'job_started, job_completed');

          END;

          /

           

          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
            Fritz-OC

            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_timezone

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

            log_history

            Retention period in days for job and window logs

            max_job_slave_processes

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

            current_open_window

            Name of the currently open window

            event_expiry_time

            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
              GregV

              Hi,

               

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