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