utl_smtp won't send mail

Dear all,

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)

IS
mailhost VARCHAR2(100) := 'oracleserver';
c UTL_SMTP.Connection;

BEGIN
dbms_output.put_line('message: '|| message);

c :=UTL_SMTP.open_connection(mailhost,25);
UTL_SMTP.Helo(c,mailhost);
UTL_SMTP.Mail(c,sender);
UTL_SMTP.Rcpt(c,recipient);
UTL_SMTP.Open_data(c);
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);
UTL_SMTP.Write_Data(c, utl_tcp.CRLF||message);
UTL_SMTP.close_data(c);
UTL_SMTP.quit(c);

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;

exp_date Date;
day_left Number(3);
grace_day Number(3) := 12;
mesg Varchar2(2000) := null;
email_list varchar2(2000) := 'user1@yahoo.com,user2@yahoo.com,user3@yahoo.com';
sender varchar2(2000) := 'oracle@oracleserver.com';
host_name SYS.v_$instance.host_name%type;

Begin
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);
End If;
End Loop;

select host_name Into host_name
from sys.v_$instance;

sendmail (SENDER => sender,
RECIPIENT => email_list,
SUBJECT => host_name||': Password Notification',
MESSAGE => mesge);
Exception
When others then
dbms_output.put_line ('Main: '||sqlerrm);
End;
/


When I execute the ck_expiry procedure, I got no email but these returns:
message: MSGING-7
METARW-7
METARO-7
METADW-7
META-7

If I execute sendmail procedure directly I got email:
exec sendmail (sender=>'oracle@oracleserver.com',recipient=>'user1@yahoo.com',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.


Thanks,
Unna

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.

Pavb,

Thanks!

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?


Thanks,
Unna