Oracle logon trigger not being fired.

Question

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?

Answer 1

I think you should have your DBA make those changes rather than relying on a trigger. You could run into issues if it ever becomes invalid for some reason.

Answer 2

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?

Answer 3

That's sorted SQL Developer. I found a solution for the .NET app when looking more into the environment variables solution; I updated the SPFILE and now a whole bunch of queries that had case issues are running correctly.

The commands I ran were:

ALTER SYSTEM SET NLS_COMP=LINGUISTIC SCOPE SPFILE;
ALTER SYSTEM SET NLS_SORT=BINARY_AI SCOPE SPFILE;

Answer 4

Just as an FYI, you'll need to be careful about manipulating session variables in order to handle case-insensitive search.

If you issue queries across a db-link to a database instance that is Oracle version 9.2 or earlier with these session variables, you'll get ORA-00600 errors because the session variable you are manipulating in Oracle 10.2 don't exist in Oracle 9.2 or earlier.