Skip to Main Content
  • Questions
  • Oracle utl_smtp to send e-mail Issue while increasing the number of emails in CC

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Najeebullah.

Asked: February 20, 2017 - 8:10 am UTC

Last updated: February 22, 2017 - 1:10 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Recently I moved to oracle database Release 12.1.0.2.0, while using below mentioned code for send email through utl_smtp it works fine but when the number of emails in cc increase it generate the error ora-29278 smtp transient error 421 service not available.
Same code running fine on oracle Release 10.2.0.1.0.

Kindly guide me which change I have to made to run the code with more then 100 numbers of email in CC.


CREATE OR REPLACE PROCEDURE send_mail_image (p_to          IN VARCHAR2,
                                           p_from        IN VARCHAR2,
                                           p_cc          IN VARCHAR2,
                                           p_subject     IN VARCHAR2,
                                           p_text_msg    IN VARCHAR2 DEFAULT NULL,
                                           p_attach_name IN VARCHAR2 DEFAULT NULL,
                                           p_attach_blob IN BLOB ) is
    conn                 utl_smtp.connection;
    BOUNDARY             VARCHAR2 (256) := '-----090303020209010600070908';
    i                    pls_integer;
    len                  pls_integer;
    buff_size            pls_integer := 57;
    l_raw                raw(57);
    p_image              blob  := p_attach_blob;
    p_smtp_host          VARCHAR2(30) := '192.168.11.6';
    l_message            VARCHAR2(32767) :=  '<html>

<body>
<br>'||p_text_msg||'
</p>

</body>
<img src="cid:banner" alt="banner"/>
</html>
';
v_emails   varchar2(5000);
v_pos      number := 0 ;
v_email    varchar2(100);

begin

conn := utl_smtp.open_connection(p_smtp_host, 25);
UTL_SMTP.helo (conn, p_smtp_host);
UTL_SMTP.mail (conn, p_from);
--UTL_SMTP.rcpt (conn, p_to);

if p_to is not null then

 v_emails  := p_to;

  Begin

    v_pos := instr(v_emails,';') ;
    if v_pos = 0 then
      v_emails := v_emails||';';
    end if;

    v_pos := instr(v_emails,';') ;

    if v_pos > 0 then
      v_email := substr(v_emails,1,v_pos-1);
         while v_pos > 0 loop
           v_pos := v_pos + 1;
           UTL_SMTP.rcpt (conn,v_email);
           v_emails := substr(v_emails,v_pos);
           v_pos := instr(v_emails,';') ;
           v_email := substr(v_emails,1,v_pos-1);
         end loop;
     End if;
  end;
  v_email  := null;
  v_emails := null;
End if;

if p_cc is not null then
v_emails  := p_cc;
  Begin
    v_pos := instr(v_emails,';') ;

    if v_pos = 0 then
      v_emails := v_emails||';';
    end if;

    v_pos := instr(v_emails,';') ;

    if v_pos > 0 then
      v_email := substr(v_emails,1,v_pos-1);
         while v_pos > 0 loop
           v_pos := v_pos + 1;
           UTL_SMTP.rcpt (conn,v_email);
           v_emails := substr(v_emails,v_pos);
           v_pos := instr(v_emails,';') ;
           v_email := substr(v_emails,1,v_pos-1);
         end loop;
     End if;
  end;
  v_email  := null;
  v_emails := null;
End if;

UTL_SMTP.open_data (conn);
UTL_SMTP.write_data (conn, 'From' || ': ' || p_from || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, 'To' || ': ' || p_to || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, 'Cc' || ': ' || p_cc || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, 'MIME-Version: 1.0' || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, 'Subject: '||p_subject || UTL_TCP.CRLF) ;
UTL_SMTP.write_data (conn, 'Content-Type: multipart/mixed; boundary="' || BOUNDARY || '"' || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, UTL_TCP.CRLF);
UTL_SMTP.write_data (conn,  '--' || BOUNDARY || UTL_TCP.CRLF );
UTL_SMTP.write_data (conn,  'Content-Type: text/html; charset=US-ASCII'|| UTL_TCP.CRLF );
UTL_SMTP.write_data (conn, UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, l_message);
UTL_SMTP.write_data (conn, UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, '--' || BOUNDARY || UTL_TCP.CRLF );
UTL_SMTP.write_data (conn, 'Content-Type: image/jpg;'|| UTL_TCP.CRLF );
UTL_SMTP.write_data (conn, 'Content-Disposition: inline; filename= "'|| p_attach_name ||'"'|| UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA (conn, 'Content-ID: <banner> ' || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, 'Content-Transfer-Encoding' || ': ' || 'base64' || UTL_TCP.CRLF);
UTL_SMTP.write_data (conn, UTL_TCP.CRLF);


i := 1;
len := dbms_lob.getlength(p_image);
while i < len
loop
  dbms_lob.read(p_image, buff_size, i, l_raw);
  utl_smtp.write_raw_data(conn, utl_encode.base64_encode(l_raw));
  utl_smtp.write_data(conn, utl_tcp.crlf);
  i := i + buff_size;
end loop;


utl_smtp.write_data(conn, utl_tcp.crlf);
    UTL_SMTP.write_data (conn, '--' || BOUNDARY || '--' || UTL_TCP.CRLF);

UTL_SMTP.write_data (conn, UTL_TCP.CRLF);
UTL_SMTP.close_data (conn);
UTL_SMTP.quit (conn);



end Send_mail_image;

and Connor said...

Sorry, I dont have 100+ email addresses that I can test this with.

It does not appear to be an inherent limitation in the interface, because I did 500 calls to utl_smtmp.rcpt (albeit all with the same address) without any problems.

So it *might* be a limitation/restriction of the mail server/configuration you have, or it *might* be an issue when the recipients are all distinct (which I can't really test).

In either case, it would strike me as odd that you would be managing this from the database - normally for volume like that, you would have a group setup as a distribution list.

But I'd recommend logging a call with Support - they might be able to assist you further.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Najeebullah Soomro, February 21, 2017 - 5:11 am UTC

Thanks McDonald for reply !

This code is running fine on oracle release 10.2.0.1.0 & sending email correctly, but generating error on oracle release 12.1.0.2.0, if their is any limitation on mail server then why it run through oracle release 10.2.0.1.0 ?

I remember that I changed one parameter on 10g from 1024 to 2048 to increase the size of email send through utl_smtp, but I don't remember which was that parameter, will you guide me to update that parameter ?

Connor McDonald
February 22, 2017 - 1:10 am UTC

I was wrong when I said:

"It does not appear to be an inherent limitation in the interface..."

because I should have read the docs more carefully :-)


I think you might have just got lucky with 10.2 because the docs for both 12c and 10.2 say

"The maximum total number of recipients that must be buffered is 100 recipients."

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_smtp.htm#i1006675


More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here