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) := '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;