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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date