Re: ORA-01843: not a valid month

From: Sanjay Mishra <"Sanjay>
Date: Thu, 19 Jan 2023 03:57:40 +0000 (UTC)
Message-ID: <1709014998.2863961.1674100660283_at_mail.yahoo.com>

Mark
Thanks for the update. Issue is updated to application team to change the format to include timestamp but not able to find as how it is working in Three lower Dev/SIT/UAT without error but failing in Perf. ThanksSanjay

On Wednesday, January 18, 2023 at 03:36:21 PM EST, Mark W. Farnham <mwf_at_rsiz.com> wrote:

Was this solved? The oracle-base article is excellent, but it is a tad frustrating that error report is ‘invalid month’ without puking out the format string and the string value of the timestamp value.

That is sort of tragic since both those values must be in hand AND they could be puked out without even increasing the vertical space.

Sigh. MON versus MM seems like like a common visual error to miss, sort of like typing the same word twice in a row.

Good luck, and so sorry about thinking my comment about fractional seconds would be funny.

mwf

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra ("smishra_97") Sent: Tuesday, January 17, 2023 5:00 PM
To: Oracle-L Freelists; Ilmar Kerm
Subject: Re: ORA-01843: not a valid month

Sorry it was my mistake to forgot to mention that cdatetime is TIMESTAMP column

SQL> select cdatetime from ptran.qtransaction where rownum < 5;

PXCREATEDATETIME

19-SEP-22 09.59.48.503000 AM 19-SEP-22 09.59.48.645000 AM 19-SEP-22 10.06.49.518000 AM 19-SEP-22 10.06.49.597000 AM

SQL> _at_desc ptran.qtransaction

Name Null? Type

           ------------------------------- -------- ----------------------------

1 CXKEY VARCHAR2(255 CHAR) 2 CXCOUNT NUMBER(18) 3 CXAMOUNT NUMBER(18) 4 CXSYSTEMID NUMBER(18) 5 CDATETIME TIMESTAMP(6) 6 CXPNAME VARCHAR2(128 CHAR)

On Tuesday, January 17, 2023 at 04:28:01 PM EST, Ilmar Kerm <ilmar.kerm_at_gmail.com> wrote:

Since you haven't sent the full DDL from both environments, will take an assumption, that what you write is correct and cdatetime column is of type TIMESTAMP...

Then constant '2023-01-12 01:01:01.122' should be converted to TIMESTAMP according to (SELECT value FROM nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT') - from the trace file it shows the value DD-MON-RR HH.MI.SSXFF AM. Which is not how the constant is formatted, so the error message is correct.

SQL> alter session set nls_timestamp_format='DD-MON-RR HH.MI.SSXFF AM';

Session altered.

SQL> select * from dual where sys_extract_utc(systimestamp) < '2023-01-12 01:01:01.122';

Error starting at line : 98 in command - select * from dual where sys_extract_utc(systimestamp) < '2023-01-12 01:01:01.122' Error report -
ORA-01843: not a valid month

And bug should be in the Dev environment. Could it be that cdatetime has a different datatype in Dev?

It almost matches NLS_DATE_FORMAT, but it is missing the fractional seconds and you should get - ORA-01830: date format picture ends before converting entire input string

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select * from dual where sysdate < '2023-01-12 01:01:01.122';

Error starting at line : 92 in command - select * from dual where sysdate < '2023-01-12 01:01:01.122' Error report -
ORA-01830: date format picture ends before converting entire input string

So... could it be that cdatetime is created as VARCHAR in dev? Wouldn't be the first time seeing that - Oracle even recommends it in Doc ID 1553906.1 (intended as a joke here, although the note exists and the rare use case is real).

On Tue, Jan 17, 2023 at 9:18 PM Sanjay Mishra <smishra_97_at_yahoo.com> wrote:

Ilmar

I am running the Test Query from the same client remotely using Sqlplus or even Toad.

Sanjay

On Tuesday, January 17, 2023 at 01:32:19 PM EST, 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:

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 19 2023 - 04:57:40 CET

This message: [ Message body ]
Next message: Scott Canaan: "Cloud Control / Checksum Algorithm"
Previous message: Mladen Gogala: "Re: ORA-01843: not a valid month"
In reply to: Mark W. Farnham: "RE: ORA-01843: not a valid month"
Next in thread: Mladen Gogala: "Re: Logical Standby"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message