I have the following code that sends an email notification to users whose passwords are going to expire in 14 days. The amount of days can be changed. However, we are looking to send 2 notifications, one at 60 days before expiration and then the last one about 14 days prior to expiration. I'm having a hard time on how to tackle this and was hoping for some suggestions.
CREATE OR REPLACE PROCEDURE ORAMON.password_expiry (
p_smtp_host IN VARCHAR2,
p_sender IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
smtpConnection UTL_SMTP.connection; -- connection info for utl_smtp
--
-- procedure for email header
--
PROCEDURE sendHeader (p_name IN VARCHAR2, p_header IN VARCHAR2)
AS
BEGIN
UTL_SMTP.write_data (smtpConnection,
p_name || ': ' || p_header || UTL_TCP.crlf);
END sendHeader;
--
-- begin main
--
BEGIN
<<cursorRecordLoop>>
FOR cursorRecord
IN (SELECT du.username,
ue.mail_addr,
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) BETWEEN TRUNC (SYSDATE)
AND TRUNC (SYSDATE)
+ INTERVAL '14' DAY)
LOOP
--
-- open mail conneciton
--
smtpConnection := UTL_SMTP.open_connection (p_smtp_host, p_smtp_port);
-- or, if using 'localhost'
-- smtpConnection := UTL_SMTP.open_connection ('localhost');
UTL_SMTP.helo (smtpConnection, p_smtp_host);
UTL_SMTP.mail (smtpConnection, p_sender);
UTL_SMTP.rcpt (smtpConnection, cursorRecord.mail_addr);
--
UTL_SMTP.open_data (smtpConnection);
--
password_expiry.sendHeader ('Subject', 'Password Expiration Notice');
UTL_SMTP.write_data (
smtpConnection,
'The password is going to expire in '
|| TO_CHAR (cursorRecord.expiry_grace_days)
|| ' days for the following account:'
|| UTL_TCP.crlf);
UTL_SMTP.write_data (smtpConnection, UTL_TCP.crlf);
UTL_SMTP.write_data (
smtpConnection,
'Username: ' || cursorRecord.username || UTL_TCP.crlf);
UTL_SMTP.write_data (
smtpConnection,
'Expiry date: '
|| TO_CHAR (cursorRecord.expiry_date, 'MM/DD/YYYY HH:MI:SS AM')
|| UTL_TCP.crlf);
UTL_SMTP.write_data (
smtpConnection,
'Email: ' || cursorRecord.mail_addr || UTL_TCP.crlf);
UTL_SMTP.close_data (smtpConnection);
END LOOP cursorRecordLoop;
--
-- close email body
--
UTL_SMTP.quit (smtpConnection);
END password_expiry;
/