Home » Tip Bank » Database Development » SQL » PL/SQL » SQL

Language: Enterprise
Expertise: Beginner

Sep 19, 2000

The following code (based on the UTL_SMTP package supplied in Oracle8i V8.1.6, release 2) enables you to receive e-mails from the database, reporting a correct ending backup, a corrupted backup, a database fail, or any message related to database admin.

First Step: Install the necessary Java classes
_____________

The typical installation automatically installs them, but if you chose a custom installation perform the following commands:

 Connect as SYS User. 
		Run %ORACLE_HOME/javavm/
plsql/jlib/initjvm.sql 
		Run %ORACLE_HOME/rdbms/
java/install/initplsj.sql

Second Step: Write the PL/SQL code
______________

Write the following PL/SQL Code:

 CREATE OR REPLACE PROCEDURE send_mail
		(  sender     IN VARCHAR2,
		   recipient  IN VARCHAR2,
		   subject    IN VARCHAR2,
		   message    IN VARCHAR2)
		IS
		  mailhost     VARCHAR2(30) := 
'YOUR.SMTP.SERVER';
		  mail_conn    utl_smtp.connection; 
  
 		 crlf VARCHAR2( 2 ):= CHR( 13 ) 
|| CHR( 10 );
		 mesg VARCHAR2( 1000 );
		
		BEGIN
		 
		  mail_conn := utl_smtp.open_connection
(mailhost, 25);
 	          mesg:= 'Date: ' || TO_CHAR( SYSDATE, 
'dd Mon yy 
hh24:mi:ss' ) || crlf ||
	         'From:  <'||sender||'>' || crlf ||
        	 'Subject: '||subject || crlf ||
	         'To: '||recipient || crlf ||
        	 '' || crlf || message;
		  utl_smtp.helo(mail_conn, mailhost);
		  utl_smtp.mail(mail_conn, sender);
		  utl_smtp.rcpt(mail_conn, recipient);
		  utl_smtp.data(mail_conn, mesg);
		  utl_smtp.quit(mail_conn);
		
		END;

It's ready to use. Try it from SQLPlus:

 begin

 send_mail('sender e-mail','dest e-mail','here 
goes subject','here goes 
message');

end;
/