ORA-01878: specified field not found in datetime or interval
A reader, May 17, 2022 - 3:25 am UTC
Greetings !
One user reported this error when they ran the query from SQL Developer tool.
Error message:
ORA-12801: error signaled in parallel query server P005, instance p101.int.com:MGPRD1 (1)
ORA-01878: specified field not found in datetime or interval
12801. 00000 - "error signaled in parallel query server %s"
*Cause: A parallel query server reached an exception condition.
*Action: Check the following error message for the cause, and consult
your error manual for the appropriate action.
*Comment: This error can be turned off with event 10397, in which
case the server's actual error is signaled instead.
Query:
select * from GES2.RM_WFB_C where gender_code='Female Global' and for_year=2021;
GES2.RM_WFB_C ==> is the database view
for_year ==> is the column extracted from the extract function inside the view as
EXTRACT (YEAR FROM P.EFF_DATE_START)
EFF_DATE_START is the TIMETAMP(6) datatype. No timezone data is stored.
I ran the same query in US EST Time Zone, I am not able to reproduce the error and user who reported is in Israel and it seems he is getting this error sporadically (not all the time).
Just wondering, what could be causing this error. Can it be user's local NLS / timestamp settings on the client side causing this error Or its underlying data issue.
Appreciate your insights / assistance on this to troubleshoot this issue further.
May 17, 2022 - 3:41 pm UTC
This is a date conversion error, so there are almost certainly implicit conversions somewhere in the query. Meaning setting NLS settings will kick in and affect the process.
ORA-01878: specified field not found in datetime or interval
A reader, May 17, 2022 - 8:13 pm UTC
>> This is a date conversion error, so there are almost certainly implicit conversions somewhere in the query. Meaning setting NLS settings will kick in and affect the process.
Could you please explain this little bit more.
If this is NLS settings issue, any suggestions on how to troubleshoot this further.
Thanks!
May 18, 2022 - 1:16 pm UTC
Ultimately you have to dig through the code and data to see where the error occurs. Capturing the exact state of the user's session when the error occurs is a good start - NLS settings, search values, etc. Then use the exact same settings when debugging.
Check all expressions involving date, timestamp, or interval values anywhere in the query - SELECT, WHERE, ORDER BY, etc. Any time you have a function or comparison with these data types you could have an implicit conversion.