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/

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);, 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;/
Share the Post:
Share on facebook
Share on twitter
Share on linkedin


Recent Articles: