SQL & PL/SQL

Hi,

I want to send email to multiple address using the Utl_Smtp feature.

When I am sending email to one email address in the To:Field it works fine.However, when I send
to multiple address I am getting the below error.I am using a table(Email_test) to store all email id.


Error report:
ORA-29279: SMTP permanent error: 501 5.1.3 Invalid address
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 240
ORA-06512: at line 48
29279. 00000 - "SMTP permanent error: %s"
*Cause: A SMTP permanent error occurred.
*Action: Correct the error and retry the SMTP operation.


I am trying two options both ways I am getting error

Option 1:
Selecting two email id indivually in the select query as below

select email into v_Recipient1 from Email_test where Key_name='U1';
select email into v_Recipient2 from Email_test where Key_name='U2';
v_Recipient := v_Recipient1||';'||v_Recipient2;

Option 2:
Is there a way to use option
select email into v_Recipient1 from Email_test where Key_name='U4';

===========================================================
Create table Script
===========================================================

create table Email_test (Email varchar2(100),Key_name varchar2(10));

insert into Email_test values ('User1@abc.com','U1');
insert into Email_test values ('User2@abc.com','U2');
insert into Email_test values ('User3@abc.com','U3')
insert into Email_test values ('User1@abc.com;User2@abc.com;User3@abc.com','U4');


select * from Email_test

User1@abc.com U1
User2@abc.com U2
User3@abc.com U3
User1@abc.com;User2@abc.com;User3@abc.com U4


select * from Email_test where Key_name in ('U1','U2','U3')
User1@abc.com U1
User2@abc.com U2
User3@abc.com U3

select * from Email_test where Key_name='U4'
User1@abc.com;User2@abc.com;User3@abc.com
=======================================================
PL/SQL Block
===========================================================


declare
v_From VARCHAR2(80) := 'abc_test@abc.com';
v_cc VARCHAR2(80);
v_Recipient VARCHAR2(80) ;
v_Recipient1 VARCHAR2(80) ;
v_Recipient2 VARCHAR2(80) ;
v_Subject VARCHAR2(80);
v_Mail_Host VARCHAR2(50);
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);

begin

--Mail Host name
select VALUE into v_Mail_Host from Server_info where server_name = 'SMTPServer';

select email into v_Recipient1 from Email_test where Key_name='U1';
select email into v_Recipient2 from Email_test where Key_name='U2';

v_Recipient := v_Recipient1||';'||v_Recipient2;

--for CC
select email into v_cc from Email_test where Key_name='U3';

v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);

utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Rcpt(v_Mail_Conn, v_cc); -- To CC recepient
utl_smtp.Rcpt(v_Mail_Conn, v_BCC); To BCC recepient

utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
'Cc: ' || v_cc || crlf ||
'Content-Type: text/html;' ||crlf ||
--'Hello this is a test email');
crlf || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf );

utl_smtp.Quit(v_mail_conn);
end;


Any suggestion how to approach this issue.
Thank you