11 Replies Latest reply: Jan 3, 2013 6:57 AM by BluShadow RSS

    sending mail to multiple users using UTL_SMTP

    817257
      HI ALL,
      i am using the following procedure to send mail to single user in oracle 11g release 2.
      i am sending it to the single user , now i need to send it to multiple users.
      i am passing single user as the input parameter, when i try to pass multiple users it gives error, invalid address.

      PROCEDURE FEEDS_SEND_MAIL (
      p_recipient IN VARCHAR2,
      p_subject IN VARCHAR2,
      p_message1 IN NUMBER DEFAULT NULL,
      p_start_stage IN NUMBER,
      p_end_stage IN NUMBER,
      p_error_desc IN VARCHAR2 DEFAULT NULL)
      AS
      l_sender varchar2(50) := '*********';
      l_mailhost VARCHAR2 (255) := '********';
      l_mail_conn UTL_SMTP.connection;
      BEGIN
      l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
      utl_smtp.ehlo(l_mail_conn, l_mailhost);
      utl_smtp.command( l_mail_conn, 'AUTH LOGIN');
      utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( '****' ))) );
      utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( '***' ))) );
      UTL_SMTP.helo (l_mail_conn, l_mailhost);
      UTL_SMTP.mail (l_mail_conn, l_sender);
      UTL_SMTP.rcpt (l_mail_conn, p_recipient);
      UTL_SMTP.open_data (l_mail_conn);
      UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || CHR(13));
      UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_sender || CHR(13));
      UTL_SMTP.write_data(l_mail_conn, 'Subject: ' ||'Status for '|| p_subject || CHR(13));
      UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_recipient || CHR(13));

      UTL_SMTP.write_data(l_mail_conn, '' || CHR(13));

      -- Multi-line message simulated with a loop calling WRITE_DATA multiple times.
      --FOR i IN 1 .. 10 LOOP
      UTL_SMTP.write_data(l_mail_conn,'Dear UAT User,'||CHR(13)||CHR(13) ||
      'Start stage for feed:'||p_start_stage ||' End stage for feeds:'||p_end_stage || CHR(13)||
      'st_oprtr_feed_proc- Records Updated :' || p_message1|| CHR(13)||
      'Error:' || p_error_desc|| CHR(13)|| CHR(13) ||CHR(13)||
      'Kind regards'||CHR(13)||
      'test mail '
      );

      UTL_SMTP.write_data(l_mail_conn, '' || CHR(13));
      UTL_SMTP.close_data (l_mail_conn);
      UTL_SMTP.quit (l_mail_conn);
      END FEEDS_SEND_MAIL;

      Edited by: sunnymoon on Feb 22, 2012 1:37 PM
        • 1. Re: sending mail to multiple users using UTL_SMTP
          Hoek
          See this example, it supports sending email to multiple recipients:
          http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:255615160805
          • 2. Re: sending mail to multiple users using UTL_SMTP
            Billy~Verreynne
            sunnymoon wrote:

            i am sending it to the single user , now i need to send it to multiple users.
            i am passing single user as the input parameter, when i try to pass multiple users it gives error, invalid address.
            That is because the RCPT TO command can only be used for specify a single recipient address at a time. If there are multiple recipients, then you need something like the following:
            // process each recipient separately
            for i in 1..recipients.Count loop
              UTL_SMTP.rcpt (l_mail_conn, recipient(i) );
            end loop;
            • 3. Re: sending mail to multiple users using UTL_SMTP
              846231
              Hi Bill,

              I can not understand how to implement this :(


              You mean, from the source program I will just replace the line :
              UTL_SMTP.rcpt (l_mail_conn, p_recipient);
              to this?
              for i in 1..recipients.Count loop
              UTL_SMTP.rcpt (l_mail_conn, recipient(i) );
              end loop;
              • 4. Re: sending mail to multiple users using UTL_SMTP
                Billy~Verreynne
                It means that for every single recipient, you need to call UTL_SMTP.rcpt().

                So if you have 3 recipients, you need 3 calls. E.g.
                UTL_SMTP.rcpt( svrHandle, '<user1@my-domain.com>' );
                UTL_SMTP.rcpt( svrHandle, '<user4@my-domain.com>' );
                UTL_SMTP.rcpt( svrHandle, '<user1@some-other-domain.com>' );
                However, it is easier to store the list of recipients in an array - and then loop through the array. E.g.
                declare
                  type TRecipients is table of varchar2(100);   --// string array type definition
                  recipientList  TRecipients; --// string array
                  ...
                begin
                  ...
                
                  --// get the recipients from somewhere, e.g. like a table
                  select
                    recipient_address bulk collect into recipientList
                  from recipients
                  where ..some condition..;
                
                  ...more mail code..
                
                  --// now specify the recipients
                  for i in 1..recipientList.Count loop
                    UTL_SMTP.rcpt( svrHandle, recipientList(i) );
                 end loop;
                
                  ...more mail code..
                
                end;
                • 5. Re: sending mail to multiple users using UTL_SMTP
                  Chanchal Wankhade
                  Hi,

                  try something like shown below....

                  PROCEDURE FEEDS_SEND_MAIL (
                  p_subject IN VARCHAR2,
                  p_message1 IN NUMBER DEFAULT NULL,
                  p_start_stage IN NUMBER,
                  p_end_stage IN NUMBER,
                  p_error_desc IN VARCHAR2 DEFAULT NULL)
                  
                  AS
                  l_sender varchar2(50) := '*********';
                  l_mailhost VARCHAR2 (255) := '********';
                  l_mail_conn UTL_SMTP.connection;
                  p_recipient VARCHAR2:='your mail id1';
                  p_recipientt VARCHAR2:='your mail id2';
                  p_recipienttt VARCHAR2:='your mail id3';
                  p_recipientttt VARCHAR2:='your mail id4';
                  BEGIN
                  utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
                  utl_smtp.Rcptt(v_Mail_Conn, v_Recipient);
                  utl_smtp.Rcpttt(v_Mail_Conn, v_Recipient);
                  utl_smtp.Rcptttt(v_Mail_Conn, v_Recipient);
                  l_mail_conn := UTL_SMTP.open_connection (l_mailhost, 25);
                  utl_smtp.ehlo(l_mail_conn, l_mailhost);
                  utl_smtp.command( l_mail_conn, 'AUTH LOGIN');
                  utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( '****' ))) );
                  utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( '***' ))) );
                  UTL_SMTP.helo (l_mail_conn, l_mailhost);
                  UTL_SMTP.mail (l_mail_conn, l_sender);
                  utl_smtp.write_DATA(v_Mail_Conn,'To: '     || v_Recipient || crlf);
                  utl_smtp.write_DATA(v_Mail_Conn,'To: '     || v_Recipientt || crlf);
                  utl_smtp.write_DATA(v_Mail_Conn,'To: '     || v_Recipienttt || crlf);
                  utl_smtp.write_DATA(v_Mail_Conn,'To: '     || v_Recipientttt || crlf);
                  --UTL_SMTP.rcpt (l_mail_conn, p_recipient);
                  UTL_SMTP.open_data (l_mail_conn);
                  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || CHR(13));
                  UTL_SMTP.write_data(l_mail_conn, 'From: ' || l_sender || CHR(13));
                  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' ||'Status for '|| p_subject || CHR(13));
                  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_recipient || CHR(13));
                  
                  UTL_SMTP.write_data(l_mail_conn, '' || CHR(13));
                  • 6. Re: sending mail to multiple users using UTL_SMTP
                    BluShadow
                    Chanchal Wankhade wrote:
                    Hi,

                    try something like shown below....

                    utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
                    utl_smtp.Rcptt(v_Mail_Conn, v_Recipient);
                    utl_smtp.Rcpttt(v_Mail_Conn, v_Recipient);
                    utl_smtp.Rcptttt(v_Mail_Conn, v_Recipient);
                    Somehow I don't think those calls are going to work.
                    • 7. Re: sending mail to multiple users using UTL_SMTP
                      Chanchal Wankhade
                      Hi,

                      Ya blu its work, I am using it.
                      • 8. Re: sending mail to multiple users using UTL_SMTP
                        Billy~Verreynne
                        Chanchal Wankhade wrote:

                        Ya blu its work, I am using it.
                        Not possible - it cannot work.

                        The code supplied above refers to procedures like utl_smtp.Rcpt<b>t</b>, utl_smtp.Rcpt<b>tt</b> and utl_smtp.Rcpt<b>ttt</b> that do not exist in package SYS.UTL_SMTP.
                        • 9. Re: sending mail to multiple users using UTL_SMTP
                          Dave Rabone
                          Global substitute is a wonderful thing
                          • 10. Re: sending mail to multiple users using UTL_SMTP
                            Chanchal Wankhade
                            Hi, Billy,

                            it works for me and i am using this mailing procedure daily.
                            • 11. Re: sending mail to multiple users using UTL_SMTP
                              BluShadow
                              Chanchal Wankhade wrote:
                              Hi, Billy,

                              it works for me and i am using this mailing procedure daily.
                              Do you have the following code in there as well...
                              WHEN OTHERS THEN
                                NULL;
                              ?