Re: Logon Trigger Error / where's the trace

marcel.kraupp_at_gmx.ch (Marcel Kraupp) wrote in message news:<332bb004.0204110117.59d00b16_at_posting.google.com>...
> Hello
>
> Here's my Oracle Version:
>
> SQL> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> PL/SQL Release 8.1.7.0.0 - Production
> CORE 8.1.7.0.0 Production
> TNS for Solaris: Version 8.1.7.0.0 - Production
> NLSRTL Version 3.4.1.0.0 - Production
>
>
>
> Now, the question:
>
> I have read http://www.jlcomp.demon.co.uk/faq/execute_on_login.html
> and seem unable to reproduce the behaviour stated in that document.
>
> Here's my logon trigger:
>
> create or replace trigger ims_logon
> after logon on database
> begin
> if user = 'IMS_SECURITY' then return; end if;
>
> raise_application_error(-20000,'My custom error');
> end;
> /
>
>
> It seems to compile fine.
>
> Then, I want to log on:
>
> SQL> connect t139080_at_imsmare
> Enter password:
> ERROR:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-20000: My custom error
> ORA-06512: at line 4
>
> Warning: You are no longer connected to ORACLE.
>
>
> Unfortunately, and condratictionary to the document, I am not connected.
> I am also unable to find the trace file that should record the error.
>
> Fortunatly, I have a backdoor in my trigger that allows me to logon
> as ims_security. If I didn't have this, what could I do if a logon trigger
> does not let anyone in?
>
>
> TIA
> Marcel

To me it looks like the tip of Connor McDonald you quote was relying on a bug, that is now resolved. The error is sent to a trace file courtesy of the internal ksedmp routine. Evidently, as you raise your own error, ksedmp shouldn't interfere and Oracle has corrected this. Your raise_application_error does what it is supposed to do: raise an error.
As after trigger are fired immediately before the actual end of the affected operation, you're now not connected. Hence, I think everything is Working As Designed. You should raise the error only if there is a true exception.

Regards,

Sybrand Bakker
Senior Oracle DBA Received on Thu Apr 11 2002 - 08:00:48 CDT