WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN
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);
create or replace procedure ck_expiry AS
Cursor c_getexpiry is
When I execute the ck_expiry procedure, I got no email but these returns:
If I execute sendmail procedure directly I got email:
exec sendmail (sender=>'email@example.com',recipient=>'firstname.lastname@example.org',subject=> 'Password Notification',message=>'MSGING-7'||chr(10)||'METARW-7'||chr(10)||'METARO-7'||chr(10)||'METADW-7'||chr(10)||'META-7');
Are there limitations for utl_smtp.write_data? How can I go around it? Any help is appreciated.
05-11-2009, 12:31 PM
Hard for me to follow ck_expiry storedproc logic.
I would expect...
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.
05-11-2009, 02:02 PM
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.