Re: Logon trigger questions

dbaplusplus_at_hotmail.com wrote:
> I am using Oracle 9.2.0.5 on HP NIX 11i but question is general.
>
>
> I am developing a logon trigger for running some alter session
> commands. However, if there is any error in execution of logon trigger,
> I will still like logon to succeed. In addition, I will like record
> Oracle error message in alert.log.
>
>
> CREATE OR REPLACE TRIGGER my_logon AFTER LOGON ON database
> BEGIN
> -- call function to use outline in some category
> my_outlnfun;
> When others
> -- write error message to alert log
> -- what happens if there is error in writing to alert log (do I need
> -- to put error message to alert log in another begin block?
>
> END;
>
> 1. Will above code do what I want?
>
> 2. What is Oracle function to write to alert log?
>
> 3. I do not know when trigger becomes invalidated instead of giving
> error? If trigger is invalidated, is only fix is to conn / as sysdba
> and disable it.

Allow me to make a very small change to your code:

CREATE OR REPLACE TRIGGER my_logon
AFTER LOGON
ON database
BEGIN

• call function to use outline in some category my_outlnfun; EXCEPTION WHEN OTHERS THEN
  • write to a logging table. You can not write to the alert log
  • if this succeeds then the error condition has been handled. END; / -- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)