Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: after servererror trigger

Re: after servererror trigger

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 28 Jul 2006 17:00:40 +0100
Message-ID: <7765c8970607280900s707235cauf9da91fc437a2be7@mail.gmail.com>


you might also think about whether any errors that will cause the trigger to fire might be triggered if the emailing doesn't work. That would be rather unfortunate....

On 7/28/06, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
>
> errrm the 9291 error surely should only be a temporary error and not one
> regular enough to email about :)
>
> for errors that don't have a user or termin al then don't try and capture
> them.
>
>
> On 7/28/06, Wojciech Skrzynecki <wojciech.skrzynecki_at_gmail.com> wrote:
>
> > Hello
> >
> >
> >
> > I would like to ask you about after servererror trigger. I wrote trigger
> > as below:
> >
> >
> >
> > CREATE OR REPLACE TRIGGER notification_error
> >
> > after servererror on database
> >
> > declare
> >
> > d_sender varchar2(30) := ora_database_name;
> >
> > d_user varchar2(30) :=ora_login_user;
> >
> > d_rcp varchar2(100) := ' test';
> >
> > d_mailhost VARCHAR2(30) := 'test';
> >
> > d_mail_conn utl_smtp.connection;
> >
> > d_terminal VARCHAR2(30) :=userenv('terminal');
> >
> > d_current_nr_error number := ora_server_error(1);
> >
> > ora_server_error_msg varchar2(100);
> >
> > BEGIN
> >
> > if d_current_nr_error between 01800 and 02231
> >
> > or d_current_nr_error in (09291,16014)
> >
> > then
> >
> > ora_server_error_msg := SQLERRM(-d_current_nr_error);
> >
> > d_mail_conn := utl_smtp.open_connection(d_mailhost);
> >
> > utl_smtp.helo(d_mail_conn, d_mailhost );
> >
> > utl_smtp.mail(d_mail_conn, d_sender);
> >
> > utl_smtp.rcpt(d_mail_conn, d_rcp);
> >
> > utl_smtp.open_data(d_mail_conn);
> >
> > utl_smtp.write_data(d_mail_conn, utl_tcp.CRLF || 'From: ' ||
> > ora_database_name);
> >
> > utl_smtp.write_data(d_mail_conn, utl_tcp.CRLF || 'Subject: ' ||
> > ora_server_error_msg);
> >
> > utl_smtp.write_data(d_mail_conn, utl_tcp.CRLF || 'User: ' ||
> > ora_login_user);
> >
> > utl_smtp.write_data(d_mail_conn, utl_tcp.CRLF || 'Terminal: ' ||
> > d_terminal);
> >
> > utl_smtp.write_data(d_mail_conn, utl_tcp.CRLF || 'Date: ' ||
> > to_char(sysdate, 'RRRR-MM-RR HH24:MI:SS') );
> >
> > utl_smtp.close_data(d_mail_conn);
> >
> > utl_smtp.quit(d_mail_conn);
> >
> > end if;
> >
> >
> >
> > I do not know how to intercept background ORA error example "ORA-01652:
> > unable to extend temp segment by 512 in tablespace TEMP" or
> >
> >
> > "ORA-9291 invalid device specified for archive destination "
> >
> >
> >
> > Could you explain me how to do this?
> >
> >
> >
> > Thanks for help
> > --
> >
> > Wojciech Skrzynecki
> > Database Administrator
> >
>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 28 2006 - 11:00:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US