Re: after logon trigger for nls-parameters

If you look at the sqlnet trace, you see sqlplus fires alter session set ... command after the completion of the connect statement.
So all your set_nls in the after logon trigger are overriden.

--
Have a nice day
Michel


Pascal Glauser <glauser_at_my-deja.com> a écrit dans le message :
8a7d36$rlk$1_at_nnrp1.deja.com...


> Dear all


>

> I try to set the nls_parameters in an after-logon-trigger (8.1.5) for

> all sessions for a certain application, to get rid of dependencies from

> settings in the registry (NT):

>

> CREATE OR REPLACE TRIGGER tr_logonaft after logon on database

> DECLARE

>     wTosca Boolean ;

>   BEGIN

>     /* skiped some logic to tell if it concerns our application */

>     wTosca := true ;

>

>     if wTosca then

>       dbms_session.set_nls('NLS_LANGUAGE','''GERMAN''') ;

>       /* needs single quoted value, inside string double them */

>       dbms_session.set_nls('NLS_TERRITORY','''SWITZERLAND''');

>       dbms_session.set_nls('NLS_DATE_FORMAT','''DD.MM.RR''');

>       dbms_session.set_nls('NLS_DATE_LANGUAGE','''GERMAN''');

>       dbms_session.set_nls('NLS_ISO_CURRENCY','''SWITZERLAND''');

>       dbms_session.set_nls('NLS_CURRENCY','''Fr.''');

>       dbms_session.set_nls('NLS_NUMERIC_CHARACTERS','''.''''');

>       dbms_session.set_nls('NLS_SORT','''BINARY''');

>     end if ;

>

>     insert into logons values (user, sysdate) ;

>     commit ;

>   END ;

> /

>

> The logon-trigger works fine, since every session puts an entry into the

> table logons. However, the nls-parameters are overridden by the

> registry-settings (I tried it using PL/SQL, only), see

> select * from nls_session_parameters.

> On my idea, SQL-Plus sets the nls-parameters according to the registry

> only after the trigger fired. Has anyone some experience or found

> something about it in the documentation ?

>

> TIA

> Pascal

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.