Email Notification - Password Expiration Showing Multiple Accounts
I've been working on this procedure to send an email when a user account is expired. I had it working using UTL_SMTP, but running into issues switching it to UTL_MAIL. At this point, when multiple accounts are expired, the email is sent to the user but with the multiple accounts in the body. It seems like it's something to do with the loop, but i'm not completely sure.
CREATE OR REPLACE PROCEDURE oramon.password_expiry_4IS v_message CLOB; v_line VARCHAR2 (4000); v_dbname VARCHAR2(8);BEGIN /*EXECUTE IMMEDIATE 'alter session set smtp_out_server = p_smtp_host '; */ v_message := '<p><b>The following account is set to expire:</b></p>'; SELECT value INTO v_dbname FROM v$parameter WHERE name = 'db_unique_name'; 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) - TRUNC (SYSDATE) = 0 OR TRUNC (du.expiry_date) - TRUNC