How to send mail to multiple users through the database?

Hi folks,

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

Code:

---

CREATE OR REPLACE PROCEDURE send_mail(
subject in VARCHAR2,
message  in VARCHAR2,
recipient in VARCHAR2
)
IS
sender VARCHAR2(200) :='[email protected]';
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:

Code:

---

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

does that smtp server exist

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.

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

Yes!