Infolinks

Wednesday 11 July 2012

How to 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) := 'onlinetraining@oracleapps4u.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