Hello,
I'm creating a procedure that, at this point, involves 2 tables: one is DBA_USERS and the other is a lookup table called TEST_USER_EMAIL. The TEST_USERS_EMAIL table contains the USERNAME (using to join with DBA_USERS) and the EMAIL_ADDRESS. The procedure will be ran as a job to email the user a notification that their password is about to expire. The procedure is working, but when I attempted to use the cursor (C1) to add the users email address to the SEND_HEADER procedure I get nothing. Once I added the following, the procedure failed:
FETCH c1 INTO acct_info;
if c1%FOUND
then
Do I need to another cursor and table type?
CREATE OR REPLACE PROCEDURE oramon.password_expiry
IS
--
-- variable declaration
--
v_chr CONSTANT CHAR := '*';
--
-- connection info for UTL_SMPT
--
l_connection UTL_SMTP.CONNECTION;
--
-- table type to hold text for expired accounts
--
TYPE acct_rec IS RECORD
(
username VARCHAR2 (30)
,mail_addr VARCHAR (100)
,expiry_date DATE
,expiry_grace NUMBER
);
--
-- table containing expired accounts
--
acct_info acct_rec;
--
-- declare cursor to store accounts with
-- expired passwords
--
CURSOR c1
IS
SELECT du.username, ue.mail_addr, du.expiry_date, TRUNC(du.expiry_date) - TRUNC(SYSDATE) as expiry_grace
FROM dba_users du JOIN test_user_emails ue
ON LOWER(du.username) = LOWER(ue.username)
WHERE du.account_status IN ('OPEN', 'EXPIRED')
AND du.username NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
AND du.expiry_date BETWEEN TRUNC (SYSDATE) AND TRUNC (SYSDATE + 14);
--
-- Procedure for email header
--
PROCEDURE send_header (name IN VARCHAR2, header IN VARCHAR2)
AS
BEGIN
UTL_SMTP.WRITE_DATA (l_connection,
name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
--
-- open cursor
--
OPEN c1;
--
-- Open mail conneciton
--
FETCH c1 INTO acct_info;
if c1%FOUND
then
l_connection := UTL_SMTP.OPEN_CONNECTION ( 'localhost' ) ;
UTL_SMTP.HELO (l_connection, '<domain>');
UTL_SMTP.MAIL (l_connection, '<email_addr>');
UTL_SMTP.RCPT (l_connection, '<email_addr>);
UTL_SMTP.OPEN_DATA (l_connection);
--
-- Create email header (To:, From:, Subject:)
--
send_header ('From', 'oramon');
send_header ('To', acct_info.mail_addr);
send_header ('Subject', 'Password Expiration Notice');
end if;
--
-- fetch cursor into table and load accounts
-- with expired passwords
--
LOOP
FETCH c1 INTO acct_info;
EXIT WHEN c1%NOTFOUND;
UTL_SMTP.WRITE_DATA (l_connection, 'The password is going to expire in ' || acct_info.expiry_grace || ' days for the following account:' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA (l_connection, UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA (l_connection, 'Username: ' || acct_info.username || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA (l_connection, 'Expiry Date: ' || acct_info.expiry_date || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA (l_connection, 'Email: ' || acct_info.mail_addr || UTL_TCP.CRLF);
END LOOP;
--
-- Start email body
--
-- UTL_SMTP.WRITE_DATA(l_connection, 'TEST' || UTL_TCP.CRLF);
UTL_SMTP.CLOSE_DATA (l_connection);
UTL_SMTP.QUIT (l_connection);
END;
/