Oracle Procedure to send emails to single or multiple receipients

Oracle Procedure to send emails to single or multiple recipients:

1. The below code is used to send email to single recipient using oracle Store Procedure:

 /********************************************************************************

           Function Name: sendmail_from_plsql
              Description: This function sends and Email through the system mail host
                           to the address in the "p_rcptaddr" input parameter.
             Input Params: p_sendaddr : Email address of the sender.
                           p_rcptaddr : Email address of the receipient.
                           p_ccaddr : Email address of the cc address.
                           p_subject: Subject title of the Email message.
                           p_body   : Body of the mail message.
           Output Params:  return boolean value, TRUE if sendmail successful else FALSE
               p_error_msg  - Error msg out, If there is error in sending mail
    ********************************************************************************/

FUNCTION sendmail_from_plsql (p_sendaddr    IN     VARCHAR2,
                                 p_rcptaddr    IN     VARCHAR2,
                                 p_ccaddr      IN     VARCHAR2,
                                 p_subject     IN     VARCHAR2,
                                 p_body        IN     VARCHAR2,
                                 p_error_msg      OUT VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      mailhost := 'xxxxxxxxxx';-- give your mail host name over here
      mail_conn := UTL_SMTP.open_connection (mailhost, 25);
      UTL_SMTP.helo (mail_conn, mailhost);
      UTL_SMTP.mail (mail_conn, p_sendaddr);
      UTL_SMTP.rcpt (mail_conn, p_rcptaddr);

      IF p_ccaddr IS NOT NULL
      THEN
         UTL_SMTP.rcpt (mail_conn, p_ccaddr);
      END IF;

      v_mesg :=
            'Date: '
         || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
         || v_crlf
         || 'From: '
         || p_sendaddr
         || v_crlf
         || 'To: '
         || p_rcptaddr
         || v_crlf
         || 'Cc: '
         || p_ccaddr
         || v_crlf
         || 'Subject: '
         || p_subject
         || v_crlf
         || p_body
         || v_crlf;
      UTL_SMTP.data (mail_conn, v_mesg);
      UTL_SMTP.quit (mail_conn);
      RETURN (TRUE);
   EXCEPTION
      WHEN OTHERS
      THEN
         p_error_msg := SQLERRM;
         RETURN (FALSE);
   END sendmail_from_plsql;


call the above procedure using the below code:

Here v_mail_sent must be a boolean datatype



DECLARE
   v_errmsg   VARCHAR2 (2000);
   v_true boolean;
BEGIN
  v_true:= ctar_utility_pkg.sendmail_from_plsql (' sender email_id',
                        'To email_id',
                        'CC email_id',
                        'Hello , this is a test mail',
                        'Hello , this is a test mail',
                        v_errmsg);
END;



2. The below code is used to send email to multiple recipients:



 FUNCTION address_email (p_string IN VARCHAR2, p_recipients IN array)
      RETURN VARCHAR2
   IS
      l_recipients   LONG;
   BEGIN
      FOR i IN 1 .. p_recipients.COUNT
      LOOP
         UTL_SMTP.rcpt (mail_conn, p_recipients (i));

         IF (l_recipients IS NULL)
         THEN
            l_recipients := p_string || p_recipients (i);
         ELSE
            l_recipients := l_recipients || ', ' || p_recipients (i);
         END IF;
      END LOOP;

      RETURN l_recipients;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            ' address_email  error message is  ' || SQLERRM);
   END address_email;
  /*********************************************************************************
         Function Name: sendmail_to_multireceipents
            Description: This function sends and Email through the system mail host
                         to multiple address in the "p_rcptaddr" input parameter.
           Input Params: p_sendaddr : Email address of the sender.
                         p_to : Long List of Email address of the receipients.
                         p_cc :Long list of Email address of the cc addresses.
                         p_bcc :Long list of Email address of the bcc addresses.
                         p_subject: Subject title of the Email message.
                         p_body   : Body of the mail message.
  *******************************************************************************/


   PROCEDURE sendmail_to_multireceipents (
      p_sendaddr   IN VARCHAR2,
      p_to         IN array DEFAULT array (),
      p_cc         IN array DEFAULT array (),
      p_bcc        IN array DEFAULT array (),
      p_subject    IN VARCHAR2,
      p_body       IN LONG)
   AS
      l_to_list    LONG;
      l_cc_list    LONG;
      l_bcc_list   LONG;

      PROCEDURE writeData (p_text IN VARCHAR2)
      AS
      BEGIN
         IF (p_text IS NOT NULL)
         THEN
            UTL_SMTP.write_data (mail_conn, p_text || v_crlf);
         END IF;
      END;

   BEGIN
      mailhost := 'XXXXX';          --       mail host name
      mail_conn := UTL_SMTP.open_connection (mailhost, 25);

      UTL_SMTP.helo (mail_conn, mailhost);
      UTL_SMTP.mail (mail_conn, p_sendaddr);
      l_to_list := address_email ('To: ', p_to);
      l_cc_list := address_email ('Cc: ', p_cc);
      UTL_SMTP.open_data (mail_conn);
      writeData ('Date: ' || execution_date);
      writeData ('From: ' || p_sendaddr);
      writeData ('Subject: ' || NVL (p_subject, '(no subject)'));


      writeData (l_to_list);
      writeData (l_cc_list);

      UTL_SMTP.write_data (mail_conn, '' || v_crlf);
      UTL_SMTP.write_data (mail_conn, p_body);
      UTL_SMTP.close_data (mail_conn);
      UTL_SMTP.quit (mail_conn);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            ' sendmail_to_multireceipents error message is  ' || SQLERRM);
   END sendmail_to_multireceipents;


Code to call the above procedure is :


BEGIN
   ctar_utility_pkg.sendmail_to_multireceipents (
      p_sendaddr   => 'Sender email id',
      p_to             => ctar_utility_pkg.array ('To email id's'),
      p_cc             =>  ctar_utility_pkg.array ('CC email id'),
      p_bcc            =>  NULL,
      p_subject        => 'Test Mail',
      p_body           => 'Hello , this is a test mail');
END;
/