Login | Register   
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: Enterprise
Expertise: Beginner
Sep 19, 2000

Code to Send E-Mails from an Oracle Stored Procedure

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; /
Jose Antonio
 
Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap