Re: ORA-01843: not a valid month

Oh, I forgot to mention that you can use that modified_parameters() action to dump nls settings of another session where you get that error message. So you can find wrong settings and ask your collegues to change them as you need

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE
http://orasql.org

On Tue, 17 Jan 2023, 18:32 Ilmar Kerm, <ilmar.kerm_at_gmail.com> wrote:

> NLS session parameters are set by the client - check client OS
> environment, java regional settings, ....
>
> On Tue, Jan 17, 2023 at 6:46 PM Sanjay Mishra <smishra_97_at_yahoo.com>
> wrote:
>
>> Thanks Ilmar for the details. I am trying to check what is different in
>> the environment as Code/SQL is generated using 3rd Party application and so
>> cannot change, It is working in a lower environment but failed in Perf
>> testing. I can see that using timestamp or nls_timestamp_format, it is
>> working but as code cannot be changed and so was trying to see what is
>> impacting it
>>
>> Sanjay
>> On Tuesday, January 17, 2023 at 12:22:02 PM EST, Ilmar Kerm <
>> ilmar.kerm_at_gmail.com> wrote:
>>
>>
>> You pass the timestamp value as a string not as a timestamp, so it is
>> subject to SESSION nls timestamp formatting rules.
>> Never rely on "magic" implicit datatype conversions - you'll be subject
>> to inconsistent behaviour like this.
>> Instead of '2023-01-12 01:01:01.122' you can write timestamp'2023-01-12
>> 01:01:01' or use to_timestamp() function.
>>
>>
>> https://oracle-base.com/blog/2020/07/08/when-implicit-date-conversions-attack/
>> "Relying on an implicit conversion is *ALWAYS* a bug waiting to happen."
>>
>>
>> On Tue, Jan 17, 2023 at 6:03 PM Sanjay Mishra <
>> dmarc-noreply_at_freelists.org> wrote:
>>
>> Hi
>>
>> I had Dev and Perf setup where one of the Application Column is TIMESTAMP
>> and the query is giving an error in Perf but working in Dev
>>
>> select cname,cvalue from ptran.qtransaction where cdatetime <=
>> '2023-01-12 01:01:01.122'
>> ERROR at line 1:
>> ORA-01843: not a valid month
>>
>> It is working fine in other database
>> Database Parameter are same in both
>> NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
>> NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
>> NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
>> NLS_TIME_FORMAT HH.MI.SSXFF AM
>>
>> show parameter nls
>> SQL> show parameter nls
>>
>> PARAMETER_NAME TYPE
>> VALUE
>> ------------------------------------------------------------ -----------
>> ----------------------------------------------------------------------------------------------------
>> nls_calendar string
>> GREGORIAN
>> nls_comp string
>> BINARY
>> nls_currency string $
>> nls_date_format string
>> YYYY-MM-DD HH24:MI:SS
>> nls_date_language string
>> AMERICAN
>> nls_dual_currency string $
>> nls_iso_currency string
>> AMERICA
>> nls_language string
>> AMERICAN
>> nls_length_semantics string
>> CHAR
>> nls_nchar_conv_excp string
>> FALSE
>> nls_numeric_characters string
>> .,
>> nls_sort string
>> BINARY
>> nls_territory string
>> AMERICA
>> nls_time_format string
>> HH.MI.SSXFF AM
>> nls_time_tz_format string
>> HH.MI.SSXFF AM TZR
>> nls_timestamp_format string
>> DD-MON-RR HH.MI.SSXFF AM
>> nls_timestamp_tz_format string
>> DD-MON-RR HH.MI.SSXFF AM TZR
>>
>>
>> TIA
>> Sanjay
>>
>>
>>
>> --
>> Ilmar Kerm
>>
>
>
> --
> Ilmar Kerm
>

--
http://www.freelists.org/webpage/oracle-l