send a mail if insert operation fails

**milonov** · Nov 7 '06, 10:17 AM

Hi, Archana.

You can use UTL_SMTP oracle package:

To enable UTL_SMTP in the database java must be enabled run

$ORACLE_HOME/javavm/install/initjvm.sql
$ORACLE_HOME/javavm/install/init_jis.sql
$ORACLE_HOME/rdbms/admin/initplsj.sql

then use function below:

Code:

CREATE OR REPLACE PROCEDURE send_mail (
pSender    VARCHAR2,
pRecipient VARCHAR2,
pSubject   VARCHAR2,
pMessage   VARCHAR2) IS

mailhost  CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com';
crlf      CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg      VARCHAR2(1000);
mail_conn utl_smtp.connection;

BEGIN
   mail_conn := utl_smtp.open_connection(mailhost, 25);

   mesg := 'Date: ' ||
        TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
           'From: <'|| pSender ||'>' || crlf ||
           'Subject: '|| pSubject || crlf ||
           'To: '||pRecipient || crlf || '' || crlf || pMessage;

   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, pSender);
   utl_smtp.rcpt(mail_conn, pRecipient);
   utl_smtp.data(mail_conn, mesg);
   utl_smtp.quit(mail_conn); 
EXCEPTION
  WHEN INVALID_OPERATION THEN
    NULL;
  WHEN TRANSIENT_ERROR THEN
    NULL;
  WHEN PERMANENT_ERROR THEN
    NULL;
  WHEN OTHERS THEN
    NULL; 
END send_mail;
/

Best Regards,
Michael Milonov

http://www.snotratech.com

Originally posted by hiarchu21

I have a dbms job, which will insert data from source to target table.
after insertion, a mail has to be sent to admin.

1) if it failed, fail message has to be sent.
2) if succeeded ,success message has to be sent to admin.

Can anybody please let me know which code i have to write and where i have to append this in the dbms job?

Thanks in advance.
Archana

**hiarchu21** · Nov 8 '06, 10:20 AM

Originally posted by milonov

Hi, Archana.

You can use UTL_SMTP oracle package:

To enable UTL_SMTP in the database java must be enabled run

$ORACLE_HOME/javavm/install/initjvm.sql
$ORACLE_HOME/javavm/install/init_jis.sql
$ORACLE_HOME/rdbms/admin/initplsj.sql

then use function below:

Code:

CREATE OR REPLACE PROCEDURE send_mail (
pSender    VARCHAR2,
pRecipient VARCHAR2,
pSubject   VARCHAR2,
pMessage   VARCHAR2) IS

mailhost  CONSTANT VARCHAR2(30) := 'smtp01.us.oracle.com';
crlf      CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg      VARCHAR2(1000);
mail_conn utl_smtp.connection;

BEGIN
   mail_conn := utl_smtp.open_connection(mailhost, 25);

   mesg := 'Date: ' ||
        TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
           'From: <'|| pSender ||'>' || crlf ||
           'Subject: '|| pSubject || crlf ||
           'To: '||pRecipient || crlf || '' || crlf || pMessage;

   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, pSender);
   utl_smtp.rcpt(mail_conn, pRecipient);
   utl_smtp.data(mail_conn, mesg);
   utl_smtp.quit(mail_conn); 
EXCEPTION
  WHEN INVALID_OPERATION THEN
    NULL;
  WHEN TRANSIENT_ERROR THEN
    NULL;
  WHEN PERMANENT_ERROR THEN
    NULL;
  WHEN OTHERS THEN
    NULL; 
END send_mail;
/

Best Regards,
Michael Milonov
http://www.snotratech.com

Thanks Michael.
I tried the same. But got this error,
ORA-29278: SMTP transient error: