5 Replies Latest reply: Nov 6, 2011 9:06 PM by RnR RSS

    Send Email

    451107
      I have created job using dbms_scheduler. I wanted to send a email when the job is failed. Can someone help me

      Thanks
      Ravi
        • 1. Re: Send Email
          54033
          CREATE OR REPLACE FUNCTION send_email (
          input_recipient IN VARCHAR2,
          input_sender IN VARCHAR2,
          input_subject IN VARCHAR2 DEFAULT NULL,
          input_message IN VARCHAR2) RETURN VARCHAR2 IS

          l_mailhost VARCHAR2(255) := '<MAIL HOST IP ADDRESS>';
          l_mail_conn utl_smtp.connection;
          crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
          l_msg_text VARCHAR2(4000);

          BEGIN
          util.get_ORACLE_constant_type_val
          l_mail_conn := UTL_SMTP.OPEN_CONNECTION(l_mailhost, <port>); --<port> Port

          UTL_SMTP.helo(l_mail_conn, l_mailhost);

          UTL_SMTP.mail(l_mail_conn, NVL(input_sender,'ORACLE'));

          UTL_SMTP.rcpt(l_mail_conn, input_recipient);

          UTL_SMTP.open_data(l_mail_conn );

          l_msg_text := 'MAIL TEXT';

          UTL_SMTP.write_data(l_mail_conn, l_msg_text);
          UTL_SMTP.close_data(l_mail_conn );
          UTL_SMTP.quit(l_mail_conn);

          RETURN NULL;
          EXCEPTION
          WHEN OTHERS THENs
          RETURN SQLERRM;
          END;
          /

          BEGIN
          -- Call the procedure
          send_email(p_sender => 'ORACLE SCHEDULAR',
          p_recipient => 'abc@abcdefgh.xyz',
          p_subject => 'SCHEDULAR JOB',
          p_message => 'SCHEDULAR JOB Completed Succesfully' );
          END;
          /
          • 2. Re: Send Email
            RnR
            Hi,

            This is just for reference. On the Scheduler webpage

            http://www.oracle.com/technology/products/database/scheduler/index.html

            under Oracle Scheduler Utilities there is a utility job notification package that allows sending e-mail on job failure/success/completion.

            -Ravi R
            • 3. Re: Send Email
              Dird
              Hi guys,

              What would I enter for mail host? Where would I find out this info?

              Mike
              • 4. Re: Send Email
                445102
                Hi,

                I know this is quite an old blog, but I am looking for the above mentioned package to use this on a 10g database. However, the link from Ravis reply above -

                http://www.oracle.com/technology/products/database/scheduler/index.html

                - does not work any more. I have searched other areas as well and found the same link several times, but no reference to the changed location. Hope you can help me out here.

                Thanks
                Daniel
                • 5. Re: Send Email
                  RnR
                  Good question. Turns out this page was moved and google doesn't know about the new location (and it doesn't seem to be connected to Oracle's home page).

                  Anyway, it is now located here

                  http://www.oracle.com/technetwork/database/enterprise-edition/index-088128.html

                  Thanks,
                  Ravi.