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;