Home » SQL & PL/SQL » SQL & PL/SQL » How to send mail to more than one people from plsql
How to send mail to more than one people from plsql [message #36680] Wed, 12 December 2001 00:04 Go to next message
Ellis
Messages: 1
Registered: December 2001
Junior Member
I use this sample to send mail from plsql
it works!!
But I want to send to more than one
mail addr.
I try to
exec send_mail(msg_to =>'aaa@tsmc.com.tw,bbb@tsmc.com.tw');
but it dosn't work,
who can help me~~
thanks
================================

CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_from varchar2 := 'oracle',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := '' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('127.0.0.1', 25); -- open the SMTP port
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
rc := utl_tcp.write_line(c, '');
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
EXCEPTION
when others then
raise_application_error(-20000,
'Unable to send e-mail message from pl/sql');
END;
/
show errors
-- Examples:
set serveroutput on

exec send_mail(msg_to =>'orafaq@oraaq.org');

exec send_mail(msg_to =>'orafaq@orafaq.org',
msg_text=>'Look Ma, I can send mail from plsql'
);

----------------------------------------------------------------------
Re: How to send mail to more than one people from plsql [message #36904 is a reply to message #36680] Wed, 02 January 2002 20:04 Go to previous message
Alli Rajan Ramachandran
Messages: 1
Registered: January 2002
Junior Member
I'm using this code to send the mail to multi id's.

""""
select sysdate into end_dt from dual;
for admin in admin_list
loop
emailids := emailids ||','|| admin.ae_emailid;
dbms_output.put_line(emailids);
end loop;

emailids := SUBSTR(emailids,2);
"""""

Allirajan

----------------------------------------------------------------------
Previous Topic: Error in Stored Procedure
Next Topic: searching tabe character in a variable always returns zero
Goto Forum:
  


Current Time: Sat May 25 09:57:19 CDT 2024