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;
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.