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;/