Send Email for Multiple Recipients with attachment from Oracle Database
PLSQL Procedure.
DECLARE
i NUMBER := 1;
j NUMBER := 1;
p_to VARCHAR2 (100);
lv_smtp_server VARCHAR2 (100) := 'qgbl-smtprelay.les.com';
lv_domain VARCHAR2 (100);
lv_from VARCHAR2 (100) := 'Donot reply-ARS@les.com';
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;
ld_msg VARCHAR2(100);
name_array DBMS_SQL.varchar2_table;
CC_parties varchar2(2000);
BEGIN
ld_date := SYSDATE;
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -2), 'MON-YYYY')|| ' Unlocked Checklists has been deleted.' INTO ld_msg FROM DUAL;
lv_domain := lv_smtp_server;
name_array(1) := null;
name_array(2) := 'abcd@les.com';
FOR i IN name_array.FIRST .. name_array.LAST
LOOP
CC_parties := CC_parties||';'|| name_array(i);
END LOOP;
BEGIN
v_clob := 'empno' || ',' || 'ename' ||',' ||'job' || UTL_TCP.crlf;
for c1 in (
SELECT 'abd@les.com' AS EID FROM DUAL)
LOOP
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, c1.eid); -- 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 (c1.eid) IS NOT NULL
THEN
UTL_SMTP.write_data (v_connection, 'To: ' || CC_parties || UTL_TCP.crlf);
UTL_SMTP.write_data (v_connection, 'Cc: ' || c1.eid|| UTL_TCP.crlf);
END IF;
END LOOP;
UTL_SMTP.write_data (v_connection,
'Subject:"'|| TO_CHAR(ADD_MONTHS(SYSDATE, -2), 'MON-YYYY')|| ' Unlocked Checklist raw data'||'"'|| 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="'
|| TO_CHAR(ADD_MONTHS(SYSDATE, -2), 'MON-YYYY')
|| ' Unlocked Checklist raw data'
|| '.csv'
|| '"'
|| UTL_TCP.crlf
);
END IF;
UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
FOR i IN (SELECT empno,ename,job from emp)
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.empno || '"'||','|| i.ename ||','|| i.job || UTL_TCP.crlf;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('You can put error, debug message here ' || SQLERRM);
END;
UTL_SMTP.write_data (v_connection, v_clob); --Writing data in csv attachment.
END LOOP;
UTL_SMTP.write_data(v_connection, '--' || c_mime_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(v_connection, '<!doctype html>
<html>
<head>
<title>Test HTML message</title>
</head>
<body>
<p> Dear Team,</p>
<p> '|| ld_msg||'</p>
<p> Please find the attached '|| TO_CHAR(ADD_MONTHS(SYSDATE, -2), 'MON-YYYY')||' unlocked checklist raw data.</p>
</br>
<p>Regards,</p>
<p>Admin Group</p>
</body>
</html>');
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;
Comments
Post a Comment