PL/SQL (MOSC)

MOSC Banner

Email Notification - Password Expiration Showing Multiple Accounts

edited Nov 19, 2018 8:49AM in PL/SQL (MOSC) 5 commentsAnswered ✓

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center