Skip to Main Content
  • Questions
  • Error while sending the attachment using UTL_SMTP mail

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, BHS.

Asked: September 06, 2017 - 6:44 am UTC

Last updated: September 09, 2017 - 12:49 am UTC

Version: 12C

Viewed 1000+ times

You Asked

Dear Tom,

We are getting error

Sqlcode : -29279 - Sqlerrm :ORA-29279: SMTP permanent error: 554 Transaction failed: Missing start boundary

while sending the attachment using UTL_SMTP.

please help us on this issue.

Thanks in advance.



and Connor said...

My guess if you are not sending the correct multi-part / boundary information

An 'attachment' is really sending a single stream of data, but the content allows a mail server/client to present it to you as attachments. So the stream might look something:

MIME-Version: 1.0
To: blah@asktom.com
Cc: blah@asktom.com
From: blah@asktom.com
Subject: The subject
Reply-To: blah@asktom.com 
Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"
--a1b2c3d4e3f2g1
content-type: text/html;
[my html data]

--a1b2c3d4e3f2g1--
content-type: [next content type]


So I suspect you've missed something out.

Nice examples here

https://oracle-base.com/articles/misc/email-from-oracle-plsql

Rating

  (1 rating)

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

Comments

BHS Pradeep, September 07, 2017 - 7:01 am UTC

Hi Tom,

Still i am getting the same issue. please review the below code which i am using and let me know the corrections.

Thanks in advance.



CREATE OR REPLACE PROCEDURE EBIZNET.send_bfile
( sender IN VARCHAR2 default '*****'
,recipient IN VARCHAR2
,subject IN VARCHAR2 DEFAULT NULL
,message IN VARCHAR2 DEFAULT NULL
,att_bfile IN OUT BFILE
,att_mime_type IN VARCHAR2 DEFAULT 'multipart/mixed'
,mailhost IN VARCHAR2 DEFAULT '*****'
)
IS
crlf CONSTANT VARCHAR2(2) := CHR(13) || CHR(10);
smtp_tcpip_port CONSTANT PLS_INTEGER := 25;
bytes_per_read CONSTANT PLS_INTEGER := 23829;
boundary CONSTANT VARCHAR2(78) := '-------5e9i1BxFQrgl9cOgs90-------';
encapsulation_boundary CONSTANT VARCHAR2(78) := '--' || boundary;
final_boundary CONSTANT VARCHAR2(78) := '--' || boundary || '--';
p_site_id varchar2(100):= 'NFL';
p_comp_id varchar2(100):='';
g_app_name varchar2(100):= 'send_bfile_';
g_status number;
mail_conn UTL_SMTP.connection;
pos PLS_INTEGER := 1;
file_length PLS_INTEGER;

diralias VARCHAR2(30);
bfile_filename VARCHAR2(512);
lines_in_bigbuf PLS_INTEGER := 0;

recipients varchar2(32767);

FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS
addr VARCHAR2(256);
i pls_integer;
FUNCTION lookup_unquoted_char(str IN VARCHAR2,
chrs IN VARCHAR2) RETURN pls_integer IS
c VARCHAR2(5);
i pls_integer;
len pls_integer;
inside_quote BOOLEAN;
BEGIN
inside_quote := false;
i := 1;
len := length(str);
WHILE (i <= len) LOOP
c := substr(str, i, 1);
IF (inside_quote) THEN
IF (c = '"') THEN
inside_quote := false;
ELSIF (c = '\') THEN
i := i + 1; -- Skip the quote character
END IF;
GOTO next_char;
END IF;
IF (c = '"') THEN
inside_quote := true;
GOTO next_char;
END IF;
IF (instr(chrs, c) >= 1) THEN
RETURN i;
END IF;
<<next_char>>
i := i + 1;
END LOOP;
RETURN 0;
END;
BEGIN
addr_list := ltrim(addr_list);
i := lookup_unquoted_char(addr_list, ',;');
IF (i >= 1) THEN
addr := substr(addr_list, 1, i - 1);
addr_list := substr(addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;
i := lookup_unquoted_char(addr, '<');
IF (i >= 1) THEN
addr := substr(addr, i + 1);
i := instr(addr, '>');
IF (i >= 1) THEN
addr := substr(addr, 1, i - 1);
END IF;
END IF;
i := lookup_unquoted_char(addr, '@');
IF (i = 0 and '*****'!= '*****') THEN
i := instr('*****', '.', -1, 2);
addr := addr || '@' || substr('*****', i + 1);
END IF;
addr := '<' || addr || '>';
RETURN addr;
END;

PROCEDURE writedata (str IN VARCHAR2, crlfs IN PLS_INTEGER DEFAULT 1)
IS
BEGIN
UTL_SMTP.write_data(mail_conn, str || RPAD(crlf, 2 * crlfs, crlf));
END;

BEGIN

g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Before fileopen', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );

DBMS_LOB.fileopen(att_bfile, DBMS_LOB.LOB_READONLY);

g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'After fileopen', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );

file_length := DBMS_LOB.getlength(att_bfile);

--mail_conn := UTL_SMTP.open_connection('*****', smtp_tcpip_port);

-- mail_conn := UTL_SMTP.open_connection('*****',587);

mail_conn := UTL_SMTP.open_connection(host => '*****',port => 587, wallet_path => 'file:D:\wallet', wallet_password => 'WalletPasswd123', secure_connection_before_smtp => FALSE);

g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Come here - 1', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );

UTL_SMTP.ehlo(mail_conn, '*****');
utl_smtp.starttls(mail_conn);
utl_smtp.command(mail_conn, 'AUTH LOGIN');

utl_smtp.command(mail_conn,utl_raw.cast_to_varchar2 (utl_encode.base64_encode(utl_raw.cast_to_raw('********'))));
utl_smtp.command(mail_conn,utl_raw.cast_to_varchar2 (utl_encode.base64_encode(utl_raw.cast_to_raw('********'))));

recipients := '******';

UTL_SMTP.mail (mail_conn, get_address(recipients));


g_status := logging.logmesg(g_app_name, null, null, 'come here too', 1, null, null, '', 'REP', null, null,NULL, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);

recipients := recipient;

while recipients is not null loop
utl_smtp.rcpt(mail_conn,get_address(recipients));
end loop;


g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Come here - 2', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );

UTL_SMTP.open_data(mail_conn);

writedata('Date: ' || TO_CHAR(SYSTIMESTAMP,
'Dy, dd Mon YYYY HH24:MI:SS TZHTZM') || crlf
|| 'MIME-Version: 1.0' || crlf
|| 'From: ' || '*****' || crlf
|| 'Subject: ' || subject || crlf
|| 'To: ' || recipient || crlf
|| 'Content-Type: multipart/mixed; boundary="' || boundary || '"', 2);

writedata(encapsulation_boundary);
writedata('Content-Type: text/plain; charset=ISO-8859-1; format=flowed');
writedata('Content-Transfer-Encoding: 7bit', 2);
writedata(message, 2);
--writedata('Content-Type: multipart/mixed; boundary="' || boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Come here - 3', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );

DBMS_LOB.filegetname(att_bfile, diralias, bfile_filename);
writedata(encapsulation_boundary);
g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Come here - 4', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );
writedata('Content-Type: '
|| att_mime_type || '; name="' || bfile_filename || '"');
writedata('Content-Transfer-Encoding: base64');
writedata('Content-Disposition: attachment; filename="'
|| bfile_filename || '"', 2);
g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Come here - 5', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );

WHILE pos < file_length
LOOP
writedata(UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode
(
DBMS_LOB.substr(att_bfile, bytes_per_read, pos))), 0);
pos := pos + bytes_per_read;
END LOOP;
g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Come here - 6', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );
writedata(crlf || crlf || final_boundary);
g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Come here - 7', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );
UTL_SMTP.close_data(mail_conn);
g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Come here - 8', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );
UTL_SMTP.QUIT(mail_conn);
g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Come here - 9', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );
DBMS_LOB.CLOSE(att_bfile);
g_status := LOGGING.logmesg (g_app_name, NULL, NULL, 'Come here - 10', 1, p_site_id, p_comp_id, 'trailer_mail', NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );
END;
/


Connor McDonald
September 09, 2017 - 12:49 am UTC

Yes, its somewhere in there :-)

Seriously...give us a test case we can work with, not just a dump of the code from your business application.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library