How to send mail to multiple users through the database?

[nir_s]

Hi folks,

I wrote the following procedure which send mail from the database:

CREATE OR REPLACE PROCEDURE send_mail(
subject in VARCHAR2,
message  in VARCHAR2,
recipient in VARCHAR2
)
IS
sender VARCHAR2(200) :='oracle@oracle.com'; 
mailhost VARCHAR2 (30) := 'smtpserver.oracle.com';
mail_conn utl_smtp.connection; 
crlf VARCHAR2 (2) := CHR (13) 
|| CHR (10); 
mesg VARCHAR2 (1000); 
BEGIN 
mail_conn := utl_smtp.open_connection (mailhost, 25); 
mesg := 'Date: ' 
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss') 
|| crlf 
|| 'From: <' 
|| sender 
|| '>' 
|| crlf 
|| 'Subject: ' 
|| subject 
|| crlf 
|| 'To: ' 
|| recipient 
|| crlf 
|| '' 
|| crlf 
|| message; 
utl_smtp.helo (mail_conn, mailhost); 
utl_smtp.mail (mail_conn, sender); 
utl_smtp.rcpt (mail_conn, recipient); 
utl_smtp.data (mail_conn, mesg); 
utl_smtp.quit (mail_conn); 
END; 
/

When I'm trying to run this procedure for with more than one recipient I failed:

exec send_mail ('test','test','reciptent_1,reciptent_2');
ERROR at line 1:
ORA-29279: SMTP permanent error: 554
... Relay
operation rejected
ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 99
ORA-06512: at "SYS.UTL_SMTP", line 241
ORA-06512: at "SYSTEM.SEND_MAIL", line 34

NOTE: reciptent_1 , reciptent_2,mailhost and sender got a real email addresses.

My database version is 10.1.0.3

Any suggestions how to overcome this problem?

Thanks in advance,
Nir

[davey23uk]

does that smtp server exist

[nir_s]

I wrote in the NOTE that reciptent_1 , reciptent_2,mailhost and sender got a real email addresses.
I changed thier values only for this thread.

[davey23uk]

well does the real one exist and is it reachable from the database server

[nir_s]

Yes!