Re: after servererror trigger

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