Description:
The utl_smtp package is used to send e-mails from any database to a valid recipient/recipients with or without attachments.
The generic code is given to send e-mail with excel attachment; it can be modified to match with any business requirement.
DECLARE
i NUMBER := 1;
j NUMBER := 1;
p_to VARCHAR2 (100) := ‘Valid Email Id’;
lv_smtp_server VARCHAR2 (100) := ‘199.1.1.77’;
lv_domain VARCHAR2 (100);
lv_from VARCHAR2 (100) := ‘Valid Domain’;
v_connection UTL_SMTP.connection;
c_mime_boundary CONSTANT VARCHAR2 (256) := ‘–AAAAA000956–‘;
v_clob CLOB;
ln_len INTEGER;
ln_index INTEGER;
ln_count NUMBER;
ln_code VARCHAR2 (10);
ln_counter NUMBER := 0;
lv_instance VARCHAR2 (100);
ln_cnt NUMBER;
ld_date DATE;
BEGIN
ld_date := SYSDATE;
lv_domain := lv_smtp_server;
BEGIN
v_clob := ‘Number’ || ‘,’ || ‘Name’ || UTL_TCP.crlf;
v_connection := UTL_SMTP.open_connection (lv_smtp_server); –To open the connection UTL_SMTP.helo (v_connection, lv_domain);
UTL_SMTP.mail (v_connection, lv_from);
UTL_SMTP.rcpt (v_connection, p_to); — To send mail to valid receipent
UTL_SMTP.open_data (v_connection);
UTL_SMTP.write_data (v_connection, ‘From: ‘ || lv_from || UTL_TCP.crlf);
IF TRIM (p_to) IS NOT NULL
THEN
UTL_SMTP.write_data (v_connection, ‘To: ‘ || p_to || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data (v_connection,
‘Subject: Item Creation Report’ || 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
);
ln_cnt := 1;
/*Condition to check for the creation of csv attachment*/
IF (ln_cnt <> 0)
THEN
UTL_SMTP.write_data
(v_connection,
‘Content-Disposition: attachment; filename=”‘
|| ‘Emp_details’
|| TO_CHAR (ld_date, ‘dd-mon-rrrr hh:mi’)
|| ‘.csv’
|| ‘”‘
|| UTL_TCP.crlf
);
END IF;
UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
FOR i IN (SELECT x.*
FROM emp x)
LOOP
ln_counter := ln_counter + 1;
IF ln_counter = 1
THEN
UTL_SMTP.write_data (v_connection, v_clob);–To avoid repeation of column heading in csv file
END IF;
BEGIN
v_clob := ‘=”‘ || i.ID || ‘”‘ || ‘,’ || i.NAME || UTL_TCP.crlf;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, SQLERRM);
END;
UTL_SMTP.write_data (v_connection, v_clob); –Writing data in csv attachment.
END LOOP;
UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
UTL_SMTP.close_data (v_connection);
UTL_SMTP.quit (v_connection);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END;
Note: To send e-mail to multiple receipents, logic should be build to loop over ‘UTL_SMTP.rcpt (l_mail_conn, p_to)’ command
Summary:
This Post explained what the steps should follow to create sending E-mail with Excel attachment using UTL_SMTP
queries?
Do drop a note by writing us at contact@doyensys.com or use the comment section below to ask your questions.
