RE: ORA-01843: not a valid month
Date: Tue, 17 Jan 2023 16:30:23 -0500
Message-ID: <545801d92aba$e8b574f0$ba205ed0$_at_rsiz.com>
.122' ain’t :SS
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 3:17 PM
To: ilmar.kerm_at_gmail.com; Sayan Malakshinov
Cc: Oracle-L Freelists
Subject: Re: ORA-01843: not a valid month
Hi Sayan
Thanks for the very useful information to debug the details. I did checked using SQLPLUS from the same client but found no difference
Perf Environment where it is Failing
SQL> alter session set events '1843 trace name MODIFIED_PARAMETERS';
Session altered.
SQL> _at_test
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
SQL> _at_trace_file_read_current.sql
TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/vcpperf/vcpperf1/trace/vcpperf1_ora_23493.trc
PAYLOAD
- CONTAINER ID:(5) 2023-01-17T15:04:00.955572-05:00
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 = "HH.MI.SSXFF AM"
nls_timestamp_format = "DD-MON-RR HH.MI.SSXFF AM"
nls_time_tz_format = "HH.MI.SSXFF AM TZR"
nls_timestamp_tz_format = "DD-MON-RR HH.MI.SSXFF AM TZR"
nls_dual_currency = "$"
nls_comp = "BINARY" _ipddb_enable = TRUE local_listener = " xxxxxx" remote_listener = "xxxxxxxxxx" listener_networks = ""
22 rows selected.
Dev Environment where working
SQL> alter session set events '1843 trace name MODIFIED_PARAMETERS';
Session altered.
SQL> _at_test
cname cvalue
- ---------------------------------------------------------------------------
XASSD 100 SQL> _at_trace_file_read_current.sql
TRACEFILE_NAME
/u01/app/oracle/diag/rdbms/vcpdev/vcpdev1/trace/vcpdev1_ora_217779.trc
PAYLOAD
- CONTAINER ID:(3) 2023-01-17T15:04:55.676718-05:00
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 = "HH.MI.SSXFF AM"
nls_timestamp_format = "DD-MON-RR HH.MI.SSXFF AM"
nls_time_tz_format = "HH.MI.SSXFF AM TZR"
nls_timestamp_tz_format = "DD-MON-RR HH.MI.SSXFF AM TZR"
nls_dual_currency = "$"
nls_comp = "BINARY" _ipddb_enable = TRUE
db_recovery_file_dest_size= 322122547200
local_listener = " yyyyyy"
remote_listener = "yyyyyy"
listener_networks = ""
23 rows selected.
On Tuesday, January 17, 2023 at 01:38:54 PM EST, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:
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-lReceived on Tue Jan 17 2023 - 22:30:23 CET