Oracle logon trigger not being fired
I'm using the following logon trigger on an Oracle 10.2 database:
CREATE OR REPLACE TRIGGER AlterSession_trg AFTER LOGON ON DATABASE BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=LINGUISTIC'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=BINARY_AI'; END AlterSession_trg;
This is intended to make case sensitive queries a thing of the past, and when I connect from PL/SQL Developer this is indeed the case. However, when I connect from SQL Developer or the ASP.NET application I'm working on queries are again case sensitive. Is there anyway that SQL Developer/.NET could be skipping over this trigger? Have I set the trigger up wrong?
Oracle SQL Developer is changing the NLS parameters based on the preferences. (Tools -> Preferences... -> Database -> NLS Parameters)
Probably your .NET application is also changing the NLS parameters, overwriting your trigger settings.
Have you tried setting NLS_COMP and NLS_SORT as environment variables?