CREATE OR REPLACE PROCEDURE send_mail_with_attach_file
(p_from IN VARCHAR2,
p_to 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_directory IN VARCHAR2,
p_file_name IN VARCHAR2
)
AS
v_mail_conn UTL_SMTP.connection;
v_boundary VARCHAR2(50) := '----=abc1234321cba=';
v_step PLS_INTEGER := 57;
-------------------------------------
--Put here real IP!
-------------------------------------
v_smtp_server CONSTANT VARCHAR2(30) := '10.20.30.40';
v_smtp_server_port CONSTANT INTEGER := 25;
--File Reading
c_max_line_width CONSTANT PLS_INTEGER DEFAULT 54;
v_amt BINARY_INTEGER := 672 * 3; /* ensures proper format; 2016 */
v_bfile BFILE;
v_file_length PLS_INTEGER;
v_buf RAW(2100);
v_modulo PLS_INTEGER;
v_pieces PLS_INTEGER;
v_file_pos pls_integer := 1;
v_to_list VARCHAR2(1000);
v_to VARCHAR2(1000);
BEGIN
v_mail_conn := UTL_SMTP.open_connection(v_smtp_server, v_smtp_server_port);
UTL_SMTP.helo(v_mail_conn, v_smtp_server);
UTL_SMTP.mail(v_mail_conn, p_from);
v_to_list := p_to;
WHILE (INSTR(v_to_list, ',') > 0) LOOP
--get first from list
v_to := SUBSTR(v_to_list, 1, INSTR(v_to_list,',')-1);
--get remaining of list
v_to_list := SUBSTR(v_to_list, INSTR(v_to_list, ',')+1);
--call rcpt for mail
UTL_SMTP.rcpt(v_mail_conn, v_to);
END LOOP;
--last element does not have trailing ','
UTL_SMTP.rcpt(v_mail_conn, v_to_list);
UTL_SMTP.open_data(v_mail_conn);
UTL_SMTP.write_data(v_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="' || v_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, p_text_msg);
UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
--Read blob
IF p_attach_blob IS NOT NULL THEN
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/v_step) LOOP
UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, v_step, i * v_step + 1))) || UTL_TCP.crlf);
END LOOP;
ELSIF p_directory IS NOT NULL AND p_file_name IS NOT NULL THEN
--Read File
v_bfile := BFILENAME(p_directory, p_file_name);
-- Get the size of the file to be attached
v_file_length := DBMS_LOB.GETLENGTH(v_bfile);
-- Calculate the number of pieces the file will be split up into
v_pieces := TRUNC(v_file_length / v_amt);
-- Calculate the remainder after dividing the file into v_amt chunks
v_modulo := MOD(v_file_length, v_amt);
IF (v_modulo <> 0) THEN
v_pieces := v_pieces + 1;
END IF;
DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
--FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/v_step) LOOP
FOR i IN 1 .. v_pieces LOOP
v_buf := NULL;
DBMS_LOB.READ(v_bfile, v_amt, v_file_pos, v_buf);
v_file_pos := I * v_amt + 1;
UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(v_buf))|| UTL_TCP.crlf);
--Exxample
--UTL_SMTP.write_data(v_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, v_step, i * v_step + 1))) || UTL_TCP.crlf);
END LOOP;
DBMS_LOB.FILECLOSE(v_bfile);
END IF;
UTL_SMTP.write_data(v_mail_conn, UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(v_mail_conn, '--' || v_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(v_mail_conn);
UTL_SMTP.quit(v_mail_conn);
END send_mail_with_attach_file;
/