Logon Trigger Error / where's the trace

From: Marcel Kraupp <marcel.kraupp_at_gmx.ch>
Date: 11 Apr 2002 02:17:22 -0700
Message-ID: <332bb004.0204110117.59d00b16@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 Received on Thu Apr 11 2002 - 04:17:22 CDT