Re: ORA-01843: not a valid month

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Tue, 17 Jan 2023 17:56:29 +0000
Message-ID: <CAOVevU5-MGQ+QfLeLyND-4wf_xyPuGR4TnYy0sfOV3mpehxOgA_at_mail.gmail.com>

Hi Sanjay,

You can use 'MODIFIED_PARAMETERS' event trace action, like I showed with 'ORA-01722: invalid number' here:
http://orasql.org/2021/05/20/oracle-diagnostic-events-cheat-sheet/

So in your case it would be:
alter session set events '1843 trace name MODIFIED_PARAMETERS';

or using new format:
alter system set events

    'kg_event[1843]

        {occurence:end_after 1}
            MODIFIED_PARAMETERS()';

Example: https://gist.github.com/xtender/f17ad6bd638a39823b0a2141b1ab006e

SQL> select * from dual where systimestamp>'2023-55-01 00:00:00'; select * from dual where systimestamp>'2023-55-01 00:00:00'

                                      *

ERROR at line 1:
ORA-01843: not a valid month

SQL> _at_tracefile_read_current.sql

TRACEFILE_NAME

---

/opt/oracle/diag/rdbms/ora19/ORA19/trace/ORA19_ora_16013.trc

PAYLOAD

---

• CONTAINER ID:(3) 2023-01-17T17:53:47.054542+00:00

DYNAMICALLY MODIFIED PARAMETERS:

  nls_language             = "AMERICAN"
  nls_territory            = "AMERICA"
  nls_sort                 = "BINARY"
  nls_date_language        = "AMERICAN"
  nls_date_format          = "yyyy-mm-dd hh24:mi:ss"
  nls_currency             = "$"
  nls_numeric_characters   = ".`"
  nls_iso_currency         = "AMERICA"
  nls_calendar             = "GREGORIAN"
  nls_time_format          = "hh24:mi:ssxff"
  nls_timestamp_format     = "yyyy-mm-dd hh24:mi:ssxff"
  nls_time_tz_format       = "hh24:mi:ssxff TZR"
  nls_timestamp_tz_format  = "yyyy-mm-dd hh24:mi:ssxff TZR"
  nls_dual_currency        = "$"
  nls_comp                 = "BINARY"

18 rows selected.

http://orasql.org/files/events/doc/event/action/oradebug-doc-event-action-MODIFIED_PARAMETERS.php

On Tue, Jan 17, 2023 at 5:47 PM Sanjay Mishra <dmarc-noreply_at_freelists.org> 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

>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

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

Received on Tue Jan 17 2023 - 18:56:29 CET