Search This Blog

Saturday, July 13, 2013

Send an E-mail from PL/SQL

How to Send an E-mail from PL/SQL

In Oracle 8i, one can use the UTL_SMTP package for sending messages from the database via e-mail. However as yet it can not be used to attach files to. If the results from the query does not exceed varchar2(4000) however, we can use the following method to send the result via e-mail from the database. 

Note: This package requires that you install java option.
 
DECLARE 
SendorAddress Varchar2(30) := 'oracleapps4all@gmail.com';
ReceiverAddress varchar2(30) := 'gangadharcharyk@trusstechnosofts.com';
EmailServer varchar2(30) := 'xxx.xxx.xxx.xxx';
Port number := 25;
conn UTL_SMTP.CONNECTION;

crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 4000 );
mesg_body varchar2(4000);
cursor c1 is
select d.deptno,count(e.empno)
from dept d,
emp e
where e.deptno(+) = d.deptno
group by d.deptno
order by 1;
BEGIN 
conn:= utl_smtp.open_connection( EmailServer, Port );
utl_smtp.helo( conn, EmailServer );
utl_smtp.mail( conn, SendorAddress);
utl_smtp.rcpt( conn, ReceiverAddress);

mesg:= 
'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From:'||SendorAddress|| crlf ||
'Subject: Sending Mail From Database' || crlf ||
'To: '||ReceiverAddress || crlf ||
'' || crlf ||
'Dept No' ||' Count ' ||crlf||
'----------------------' ||' ------' ||crlf;
for c1rec in c1 LOOP
mesg := mesg || rpad(c1rec.deptno,22,' ') ||'
'||rpad(c1rec.count,10,' ') || crlf;
end loop;
utl_smtp.data( conn, mesg );
utl_smtp.quit( conn );
END;

No comments:

Post a Comment