DECLARE
v_connection UTL_SMTP.connection;
-- mime blocks (the sections of the email body that can become attachments)
-- must be delimited by a string, this particular string is just an example
c_mime_boundary CONSTANT VARCHAR2(256) := '-----AABCDEFBBCCC0123456789DE';
v_clob CLOB := EMPTY_CLOB();
v_len INTEGER;
v_index INTEGER;
BEGIN
-- Build the contents before connecting to the mail server
-- that way you can begin pumping the data immediately
-- and not risk an SMTP timeout
FOR x IN (SELECT *
FROM all_objects
WHERE ROWNUM < 20)
LOOP
v_clob :=
v_clob
|| x.owner
|| ','
|| x.object_name
|| ','
|| x.object_type
|| ','
|| TO_CHAR(x.created, 'yyyy-mm-dd hh24:mi:ss')
|| UTL_TCP.crlf;
END LOOP;
v_connection := UTL_SMTP.open_connection(:p_smtp_server);
UTL_SMTP.helo(v_connection, :p_domain);
UTL_SMTP.mail(v_connection, :p_from);
UTL_SMTP.rcpt(v_connection, :p_to);
UTL_SMTP.open_data(v_connection);
UTL_SMTP.write_data(v_connection, 'From: ' || :p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'To: ' || :p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'Subject: test subject' || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(
v_connection,
'Content-Type: multipart/mixed; boundary="' || c_mime_boundary || '"' || UTL_TCP.crlf
);
UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
UTL_SMTP.write_data(
v_connection,
'This is a multi-part message in MIME format.' || UTL_TCP.crlf
);
UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'Content-Type: text/plain' || UTL_TCP.crlf);
-- Set up attachment header
UTL_SMTP.write_data(
v_connection,
'Content-Disposition: attachment; filename="'
|| 'your_file_name.csv'
|| '"'
|| UTL_TCP.crlf
);
UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
-- Write attachment contents
v_len := DBMS_LOB.getlength(v_clob);
v_index := 1;
WHILE v_index <= v_len
LOOP
UTL_SMTP.write_data(v_connection, DBMS_LOB.SUBSTR(v_clob, 32000, v_index));
v_index := v_index + 32000;
END LOOP;
--
-- End attachment
UTL_SMTP.write_data(v_connection, UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(v_connection);
UTL_SMTP.quit(v_connection);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;