Hi Tom,

I have been reviewing your blog since I find it very interesting, especially the article of sending mails with attachments. I write to you because I have a problem with that subject in case you could throw a cable because I can not get it.

I am trying to send emails with an attachment in HTML format but with a size larger than 4000 characters. This HTML I want to attach it I have saved in a variable clob.

When I send it, there is data from the attachment that is misplaced. I think it's because I send it chopped.

Mi code is:

Parameters:
p_mails is the attach html

CREATE OR REPLACE PROCEDURE MAIL_FILES_ATTACH2 ( p_lote_pago in varchar2,
p_from_name in varchar2,
p_to_name in varchar2,
p_mail in clob,
p_resultado out varchar2
) is

v_file varchar2(500);
v_asunto varchar2(500);
v_message_body varchar2(4000);
v_message_type varchar2(100) := 'text/plain';

v_smtp_server varchar2(14) := 'localhost';
n_smtp_server_port number := 25;
conn utl_smtp.connection;

TYPE attach_info IS RECORD (
attach_name varchar2(500),
data_type varchar2(40) default 'text/plain',
attach_content clob default ''
);
TYPE array_attachments IS TABLE OF attach_info;
attachments array_attachments := array_attachments();

n_offset number;
n_amount number := 3500;--1900;
v_crlf varchar2(5) := CHR(13) || CHR(10);

v_cont number := 0;

mailclob clob;
mailclobf varchar2(4000);
longmail number;

begin

v_message_body := '
Estimados Señores.

En fichero adjunto enviamos detalle de las trasferencias bancarias emitidas a su favor.

Atentamente,

Paradores de Turismo de España S.M.E, S.A.
Departamento de Tesorería
José Abascal, 2 - 4, 6º 28003 Madrid España
';
v_cont := 1;
attachments.delete;
attachments.extend(v_cont);

for i in 1..v_cont loop

select asunto, substr(asunto, 3, instr(asunto,'-')-5)
into v_asunto, v_file
from paradores.par_notificaciones
where lote_pago = p_lote_pago
and idlin = i;

select v_file || '.html', 'text/plain', dbms_lob.substr(mail,4000,1), dbms_lob.getlength(mail)
into attachments(i).attach_name, attachments(i).data_type, mailclob, longmail
from paradores.par_notificaciones
where lote_pago = p_lote_pago
and idlin = i;

-- Open the SMTP connection ...
conn := utl_smtp.open_connection(v_smtp_server,n_smtp_server_port);
utl_smtp.helo(conn, v_smtp_server);
utl_smtp.mail(conn, p_from_name);
utl_smtp.rcpt(conn, p_to_name);

-- Open data
utl_smtp.open_data(conn);

-- Message info
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('To: ' || p_to_name || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('From: ' || p_from_name || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Subject: ' || v_asunto || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('MIME-Version: 1.0' || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: multipart/mixed; boundary="SECBOUND"' || v_crlf || v_crlf));

-- Message body
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || v_message_type || v_crlf || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(v_message_body || v_crlf));

-- Attachment Part

-- Attach info
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || attachments(i).data_type
|| ' name="'|| attachments(i).attach_name || '"' || v_crlf));
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="'
|| attachments(i).attach_name || '"' || v_crlf || v_crlf));

-- Attach body
n_offset := 1;
while n_offset < dbms_lob.getlength(p_mail) loop

utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(dbms_lob.substr(p_mail, n_amount, n_offset)));
n_offset := n_offset + n_amount;
end loop;
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('' || v_crlf));

-- Last boundry
utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND--' || v_crlf));

-- Close data
utl_smtp.close_data(conn);
utl_smtp.quit(conn);

end loop;
p_resultado := 'OK';
commit;

exception
when others then
p_resultado := 'Error en el proceso de envio de mails: '||substr(sqlerrm,1,100);
end;

Thank you,
Iker