why does sys_context....

We have installed a billing system for a city water company. It is a big system running on an Oracle 10gr2 database. They use it for customer care, billing and receipting. With offices all over the city, the water company does not control software installed on the users computers.

The challenge was to stop the users from logging on to the database using other tools like SQL*Plus and other Oracle clients other than our Application. So after thinking a bit and asking google for some answers, we decided to use a trigger to control that. I will not show the trigger but to get the module name from the trigger I used sys_context function. During our testing it worked quite well. The problem came when we discovered that some application modules were coming with null names. We tried many things but we have failed. I am all for PL/SQL Developer. In PL/SQL Developer at times the one SQL Window was showing the correct value and another window was failing. We tried on different machines and the behaviour was different even with SQL*Plus.

So we concluded the problem must be on some networking or Oracle setup. I am not a DBA so I can be excused to think that it was something else. I am very lucky my DBA was not interested in researching on that subject. I finally opted out of SYS_CONTEXT('USER_ENV', 'MODULE') and implemented the trigger with this statement.

select sid, module
into li_session, ls_sess_module
from v$session
where audsid = userenv('sessionid');

It worked no more null module names. I am now looking for someone to explain why SYS_CONTEXT was failing on some sessions.