Отправка файлов через UTL_SMTP

Рассылка файла по email
В p_to можно передавать много адресов через запятую например:

‘me@mycompany.com, dev@mycompany.com, test@mycompany.com

send_mail(p_to => 'me@mycompany.com, dev@mycompany.com, test@mycompany.com',
p_from => 'admin@mycompany.com',
p_subject => 'Report',
p_text_msg => 'Auto generated report.',
p_attach_name => 'report.xlsx',
p_attach_mime => 'text/plain',
p_attach_blob => bBLOB,
p_smtp_host => 'smtp.mycompany.com');

CREATE OR REPLACE PROCEDURE SEND_MAIL (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_blob IN BLOB DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
g_crlf CHAR(2) DEFAULT CHR(13)||CHR(10);

FUNCTION fnc_email_array(p_type IN VARCHAR2, p_email IN VARCHAR2) RETURN VARCHAR2 IS
v_txt VARCHAR2(4000) := ''; v_id NUMBER; v_dop VARCHAR2(4000) := '';
BEGIN
IF p_email IS NULL THEN RETURN NULL; END IF;
v_txt := replace(p_email,';',',');
LOOP
v_id := Instr(v_txt, ',');

IF v_id <= 0 THEN IF Length(v_dop) > 0 THEN
v_dop := v_dop||Substr(v_txt, v_id + 1);
utl_smtp.rcpt(l_mail_conn, replace(Substr(v_txt, v_id + 1),',',''));
ELSE
v_dop := v_txt;
utl_smtp.rcpt(l_mail_conn, replace(v_txt,',',''));
END IF;
EXIT;
END IF;
v_dop := v_dop||Substr(v_txt, 1, v_id - 1)||';';
utl_smtp.rcpt(l_mail_conn, replace(Substr(v_txt, 1, v_id - 1),',',''));
v_txt := Substr(v_txt, v_id + 1);
END LOOP;
RETURN v_dop;
RETURN p_type||p_email;
END;

PROCEDURE prc_email_array(p_type IN VARCHAR2, p_email IN VARCHAR2) IS
v_txt VARCHAR2(4000);
BEGIN
v_txt := fnc_email_array(p_type, p_email);
END;

PROCEDURE writeData(p_text IN VARCHAR2) AS
BEGIN
IF p_text IS NOT NULL THEN utl_smtp.write_data(l_mail_conn, p_text||g_crlf); END IF;
END;

BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
--UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.ehlo(l_mail_conn, 'corp');
UTL_SMTP.command(l_mail_conn, 'AUTH LOGIN');
UTL_SMTP.command(l_mail_conn, demo_base64.encode(utl_raw.cast_to_raw('user')));
UTL_SMTP.command(l_mail_conn, demo_base64.encode(utl_raw.cast_to_raw('pass')));
UTL_SMTP.mail(l_mail_conn, p_from);
--UTL_SMTP.rcpt(l_mail_conn, p_to);
IF p_to IS NOT NULL THEN prc_email_array('To: ', p_to); END IF;

UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

--UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
IF p_to IS NOT NULL THEN writeData( 'To: '||p_to); END IF;

UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));
END LOOP;

UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;

UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);
END;
/