Sending Email When Oracle Errors Occur

Can anyone point me to good documentation on the steps to take to set up an Oracle server so that it will generate an email any time that an Oracle error message is generated or send an email if certain specified Oracle error messages are generated.  I am working on a Sun server and would like to produce a script which will accomplish this.

The official method is OEM, it provides nice features in this area as well.
Also tried other method in test environment.
In 8i we can create event triggers like this:

CREATE or replace TRIGGER mail_errors AFTER SERVERERROR ON DATABASE
declare
mail_conn utl_smtp.connection;
BEGIN
mail_conn :=utl_smtp.open_connection('hostname',100);
utl_smtp.helo(mail_conn,'hostname');
utl_smtp.mail(mail_conn,'user@hostname');
utl_smtp.rcpt(mail_conn,'user.name@hotmail.com');
utl_smtp.data(mail_conn,'Database error!!!');
utl_smtp.quit(mail_conn);
END;
/

Jserver option must be installed and initplsj.sql is necessary to be run before creating this procedure.
dbms_standard.server_error(1) can be used to obtain error code. Be careful it will fire for every ora-xxx messages. In case of many errors it can put high load on server.

(There might be problems with utl_smtp, utl_tcp can also be used as a substitute)

Regards,
Imre

Thank you Andrew and Imre:

I would like to use OEM but it has been requested that I do this outside of OEM. I appreciate the advice and can probably solve the problem from what you have contributed here.

You can try SMTP email from the command line. I use it now to send simple emails during batch jobs for success or failure of a particular action. If you like, send me an email, and I can send you a sample of the code.

Andrew Simkovsky
Pipeline SYSOP
Senior Oracle DBA
Sunglass Hut International
Miami, FL

Hi Andrew,

Can you send me the steps for Configuring SMTP email in Solaris for trapping Oracle Errors..??

Thanks in Advance..

Regds..Mouli.
Chennai.

Andrew, I appreciate it if you can send me the procedure how to setup smtp. We use HP-ux 10.20, with Oracle 8.1.6.2 and for some reason, when I send a message to our smtp server, I get the following message:

SQL> /
begin
*
ERROR at line 1:
ORA-29540: class oracle/plsql/net/TCPConnection does not exist
ORA-06512: at "SYS.UTL_TCP", line 533
ORA-06512: at "SYS.UTL_TCP", line 199
ORA-06512: at "SYS.UTL_SMTP", line 102
ORA-06512: at "SYS.UTL_SMTP", line 121
ORA-06512: at "REPORT.SEND_MAIL", line 8
ORA-06512: at line 2

Thanks for the help
BarryB

I've had a lot of people ask me for this, so I figured I would just post the script. It has to be modified for your environment, of course. If you are running a shell script, you can have the script connect to Oracle and run this procedure. It does require that you have SMTP mail available, also.

Andrew Simkovsky
Pipeline SYSOP
Senior Oracle DBA
Sunglass Hut International
Miami, FL

SEND_MAIL.SQL (1KB)
SEND_MAIL.SQL

Thank you Andrew, I appreciate your help.

BarryB

Ensure that the UTL_SMTP package is setup by running the Java VM script

$ORACLE_HOME/javavm/install/initjvm.sql,
loading the plsql.jar (loadjava) from $ORACLE_HOME/plsql/jlib,
and
running $ORACLE_HOME/rdbms/admin/initplsj.sql

all connected internal or as SYS

Wolfgang Breitling
Oracle7, 8, 8i OCP DBA
Pipeline Sysop

Yes, thank you Wolfgang. I knew I forgot to mention something.

Andrew Simkovsky
Pipeline SYSOP
Senior Oracle DBA
Sunglass Hut International
Miami, FL