Friday, November 23, 2007

Trigger for sending mails(oracle errors) from Oracle Database to the normal user....

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

Note:Before creating the triggers the below mentioned details has to be installed...

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.
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

No comments: