Email Directly From Oracle DB

In the following, nID, is an example of how to get the message to send from the table, strEmails is a list of addresses seperated by semin colons (;).

create or replace procedure mail_function(nID number, strEmails varchar2) as
cursor c1 is select one, two, three
from my_table mt
where id = nID;

SendorAddress Varchar2(300) := ‘mail_function@oracle.db’;
ReceiverAddress varchar2(300);
tmpRecAddr varchar(300);
EmailServer varchar2(30) := ’smtp.me.com’;
Port number := 25;
conn UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 4000 );
mesg_body varchar2(4000);
i number;
j number;
BEGIN
for c1rec in c1 loop
SendorAddress := ‘mail_function@oracle.db’;
ReceiverAddress := strEmail;
conn:= utl_smtp.open_connection( EmailServer, Port );
utl_smtp.helo( conn, EmailServer );
utl_smtp.mail( conn, SendorAddress);

i := 1;
j := 1;
while instr(ReceiverAddress,’;',i) != 0 loop
tmpRecAddr := substr(ReceiverAddress,i,instr(ReceiverAddress,’;',i)-j);
i := instr(ReceiverAddress,’;',i)+1;
j := i;
utl_smtp.rcpt(conn, tmpRecAddr); –Call RCPT routine here
end loop;
tmpRecAddr := substr(ReceiverAddress,i,length(ReceiverAddress));
utl_smtp.rcpt(conn, tmpRecAddr); –Call RCPT routine here

mesg:=
‘Date: ‘||TO_CHAR( SYSDATE, ‘dd Mon yy hh24:mi:ss’ )|| crlf ||
‘From:’||SendorAddress||crlf||
‘Subject: Email From Oracle Database’||crlf||
‘To: ‘||ReceiverAddress ||crlf||
”||crlf||
‘One = ‘||c1rec.one||crlf||
‘Two = ‘||c1rec.two||crlf||
‘Three = ‘||c1rec.three||crlf;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
end loop;
END;
/