0

We are facing strange issue where nls_date_format is getting changed in a session, I want to track due to which part of code this is getting changed . Is there any Session level trigger which can capture changes in nls_date_format or is there a way to find it from awr report. It might be a dumb question but please share your thoughts and information about this .

abhishek bisht
  • 21
  • 4
  • `ALTER SESSION` cannot fire any system trigger. Even in case it would you can change the setting also by PL/SQL command `DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT', '...');`. Btw, why do you have a problem with that? Do you rely in your application on specific value of `NLS_DATE_FORMAT` (eg. by implicit conversion from string to DATE without specifying the format)? This would be a very bad design and should be changed in your code. – Wernfried Domscheit Jan 26 '17 at 07:51
  • the code which is failing is converting a date to char in format 'DD-MM-RR' which is getting picked up from select sys_context('userenv', 'NLS_DATE_FORMAT') from dual; once this conversion is done comparing it with another date . But the problem is this other date is coming in DD-MON-RR format and failing there saying invalid month. – abhishek bisht Jan 26 '17 at 18:50
  • 1
    Please show the code where you get the error. Again, your code should work independently from current NLS_DATE_FORMAT. It sounds like you compare strings when you actually compare **dates** which is always a bad idea. – Wernfried Domscheit Jan 27 '17 at 07:23

0 Answers0