<![CDATA[
Steps for enabling email functionality from the Oracle Database:
I.Install the required Java Classes:
The required Java classes are automatically installed during the typical installation of the Oracle Software, however, they are not installed during custom installation. To install the required Java classes, perform the following commands:
Connect as SYS User.
Run %ORACLE_HOME/javavm/plsql/jlib/initjvm.sql
Run %ORACLE_HOME/rdbms/java/install/initplsj.sql
II.Configure the SMTP server:
Get the SMTP server configured, the Unix Admin can help you with the configurations.
III.Write the 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) := 'smtp01.us.oracle.com';
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;
'Mailhost' is the name of the remote host when connection is established. The port number on which SMTP server is listening is usually 25.
Open_connection(): Allows open connection to an SMTP server.
Helo(): Performs initial handshaking with SMTP server after connecting.
Mail(): Initiates a mail transaction with the server. The destination is a mailbox.
Rcpt(): Specifies the recipient of an email message.
Data(): Specifies the body of an email message.
Quit(): Terminates an SMTP session and disconnects from the server
]]>