Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language:
Expertise: Intermediate
Nov 6, 2000

Send Emails From an Oracle Stored Procedure


The following code is for those who need to receive e-mails from the database to report a correct ending backup, a corrupted backup, a database fail, or any message database/admin related.
The code is based on the UTL_SMTP package supplied in Oracle8i V8.1.6 (release 2).

First step:

Installing necessary JAVA classes, they are installed with the typical installation but if you chose custom, 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 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;


And ready to use, try it from sqlplus:
 
begin

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

end;
/
Jose Antonio
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date