utl_smtp won't send mail
I am trying to send email to user to notify password expiration in my 9i database; however, I am not able to send email by using utl_smtp package.
These are what I am trying to do:
1. list all users whose passwords are expirying in 12 days
2. send one email to designated users with this list of users.
Here are my codes:
CREATE OR REPLACE PROCEDURE sendmail
( sender IN VARCHAR2,
recipient IN VARCHAR2,
subject IN VARCHAR2,
message IN varchar2)
mailhost VARCHAR2(100) := 'oracleserver';
dbms_output.put_line('message: '|| message);
UTL_SMTP.Write_Data(c, 'From: <' || sender || '>' || utl_tcp.CRLF);
UTL_SMTP.Write_Data(c, 'To: <' || recipient || '>' || utl_tcp.CRLF);
UTL_SMTP.Write_Data(c, 'Subject: ' || subject || utl_tcp.CRLF);
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
grace_day Number(3) := 12;
mesg Varchar2(2000) := null;
email_list varchar2(2000) := 'email@example.com,firstname.lastname@example.org,email@example.com';
sender varchar2(2000) := 'firstname.lastname@example.org';
For i in c_getexpiry Loop
day_left := (to_date(i.expiry_date,'dd-mm-yyyy')- to_date(sysdate,'DD-MM-YYYY'));
If (day_left <= grace_day and day_left >= 0) then
mesg := mesg||i.username||'-'|| day_left ||chr(10);
select host_name Into host_name
sendmail (SENDER => sender,
RECIPIENT => email_list,
SUBJECT => host_name||': Password Notification',
MESSAGE => mesge);
When others then
dbms_output.put_line ('Main: '||sqlerrm);
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.
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.
Last edited by PAVB; 05-11-2009 at 01:37 PM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
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.
Can you advice how I can accomplish this?
Click Here to Expand Forum to Full Width