If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
UTL_SMTP.quit(c);
raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20001, 'The following error has occured: ' || sqlerrm);
END;
/

create or replace procedure ck_expiry AS
Cursor c_getexpiry is
select username,expiry_date
from dba_users;

1- Populate the cursor with only expired account -not all accounts.
2- While looping the cursor either to send an individual email for each account or append each individual email account to the recipient's list to send a spam alike email at the end -none of these appear to happen.

Last but not least it beats me how the storedproc is going to learn the email account of each affected user in a real world scenario.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Our user accounts are not managed by the individual users but rather by application managers. That's why it works best for me to send an email to each of the managers about the expiring user accounts rather than an email to each expiring user.

In 10g, I use the same logic by using the utl_mail package, which works just fine; however, in 9i, I not not able to send mail with utl_smtp procedure. My goal is able to send an email with a list of users (multiple lines, one line per user); but it doesn't seem the utl_smtp works that way.