Invalid Logoff Trigger causes unhandled OracleException

Invalid Logoff Trigger causes unhandled OracleException

Postby hpalacio » Tue 26 Jun 2007 19:45

I am getting an unhandled CoreLab OracleException that is causing my application to crash. It is easily reproducible and I've provided some source code below.

The scenario is that the Oracle database has an invalid trigger associated to the Logoff event. Although there are compilation errors, they are treated as warnings and will allow the trigger to be executed.

It's expected that when the application closes the database connection, the trigger will be fired, and the application can continue on with it's processing. However, what is happening is after a certain amount of time (approximately 5 minutes) a Microsoft dialog box pops up stating that an unhandled exception occurred and will need to shut down the application.

Debugging revealed the following stack trace:

Code: Select all
   at a.a.b.b()
   at CoreLab.Oracle.OracleInternalConnection.a(Boolean A_0)
   at CoreLab.Common.DbConnectionPoolGroup.b(Object A_0)
   at CoreLab.Common.DbConnectionPool.RemoveObject(Object connection)
   at CoreLab.Common.DbConnectionPoolGroup.a(DbConnectionPool A_0)
   at CoreLab.Common.DbConnectionPoolGroup.a(Object A_0)
   at System.Threading._TimerCallback.TimerCallback_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading._TimerCallback.PerformTimerCallback(Object state)


Here are the steps to reproduce

1) create a trigger with the following body:
Code: Select all
create or replace TRIGGER TRIGGER1
BEFORE LOGOFF ON DATABASE
BEGIN
This_proc_doesnt_exist;
END;


2) create a C# application that opens and closes an OracleConnection associated to the same schema that the trigger is associated too. The key thing to remember here is to keep the form open after you press the button. It's not necessary to do anything else just let it sit there after you cause the connection to close. If you dispose of the form then the CoreLab objects are disposed and the exception is not created. However, leaving the form open for approximately 5 minutes (i've timed this several times) then the error will occur. I will attach a test project if necessary.

It appears that the CoreLab calls a thread to perform the disconnect and doesn't handle the exception that is thrown by the invalid trigger.

I'm using the following:
OraDirect .Net 3.20.7.0
Oracle 10g Standard Edition
Microsoft Framework .Net 2.0
Windows XP Professional SP2
hpalacio
 
Posts: 6
Joined: Fri 17 Mar 2006 15:29

Postby hpalacio » Tue 26 Jun 2007 20:27

Firing the trigger from sqlplus will display the ora-code:

C:\WINDOWS>sqlplus usr/pswd@host (EDIT)

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 26 15:18:11 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> select object_name, object_type, status
2 from dba_objects
3 where object_name = 'TRIGGER1';

OBJECT_NAME
--------------------------------------------------------------------------------

OBJECT_TYPE STATUS
------------------- -------
TRIGGER1
TRIGGER INVALID


SQL> disconnect
ERROR:
ORA-04098: trigger 'USR.TRIGGER1' is invalid and failed re-validation (EDIT)


Disconnected from Oracle Database 10g Release 10.2.0.1.0 - Production (with comp
lications)
SQL>
SQL> exit

C:\WINDOWS>
hpalacio
 
Posts: 6
Joined: Fri 17 Mar 2006 15:29

Postby Alexey » Wed 27 Jun 2007 14:14

I cannot reproduce this problem.
Please use the latest version of OraDirect .NET.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby hpalacio » Wed 27 Jun 2007 19:47

Upgrade appears to be working. Thanks
hpalacio
 
Posts: 6
Joined: Fri 17 Mar 2006 15:29

Postby Alexey » Sun 01 Jul 2007 19:46

You are welcome.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for Oracle