The procedure below sends an email to a user if their password expires. The problem I have now is that I need to send an email to multiple recipients (no distribution list) and a CC: distribution list. If the service account that is set to expire is setup with 3 users receiving an email and distribution list in the CC: field, how do i make it so 3 emails aren't sent to the distribution list?
CREATE OR REPLACE PROCEDURE ORAMON.password_expiry_tst
IS
v_message CLOB;
v_dbname VARCHAR2(8);
BEGIN
--
-- alter session to use SMTP server
--
EXECUTE IMMEDIATE 'alter session set smtp_out_server = ''test.com''';
--
-- select database name
--
SELECT value
INTO v_dbname
FROM v$parameter
WHERE name = 'db_unique_name';
--
-- loop through DBA_USERS table for expired accounts
--
FOR cursorRecord
IN (SELECT du.username,
ue.mail_addr_to,
ue.mail_addr_cc,
du.expiry_date,
TRUNC (du.expiry_date) - TRUNC (SYSDATE)
AS expiry_grace_days
FROM dba_users du
INNER JOIN db_emails ue
ON LOWER (du.username) = LOWER (ue.username)
WHERE 1 = 1
AND du.account_status IN ('OPEN', 'EXPIRED')
AND du.username NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
AND ( TRUNC (du.expiry_date) - TRUNC (SYSDATE) = 0
OR TRUNC (du.expiry_date) - TRUNC (SYSDATE) = 14))
LOOP
-- build html block
--
v_message :=
'<p><b>The following account is set to expire:</b></p>' ||
'<p></p>' ||
'<p>' ||
'Account: ' || lower(cursorRecord.username) || '</br>' ||
'Expiry Date: ' || cursorRecord.expiry_date || '</p>';
--
-- build html email
--
UTL_MAIL.SEND (sender => 'oracle@test.com',
recipients => cursorRecord.mail_addr_to,
cc => cursorRecord.mail_addr_cc,
subject => 'Password Expiration - ' || UPPER(v_dbname),
mime_type => 'text/html; charset=us-ascii', -- sending html e-mail
MESSAGE => v_message); -- html block
/*
exception
when others then
utl_mail.send(sender => l_from,
recipients => l_to,
subject => 'automated_email_alert__10g (mysid): Error',
message => 'automated_email_alert__10g (mysid) failed with the
following error:' || sqlerrm);
*/
END LOOP cursorRecordLoop;
END password_expiry_tst;
/