oracle ORA-01843: 不是 Java 应用程序中的有效月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10435412/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01843: not a valid month in Java application
提问by Carlos Gavidia-Calderon
We have a Java Web application deployed in SAP Netweaver that accesses an Oracle 10g Database through plain JDBC. The application was working fine until a few days ago, when a query was causing the following error:
我们在 SAP Netweaver 中部署了一个 Java Web 应用程序，它通过普通 JDBC 访问 Oracle 10g 数据库。该应用程序运行良好，直到几天前，当查询导致以下错误时：
ORA-01843: not a valid month
The query that's giving us trouble is this:
给我们带来麻烦的查询是这样的：
SELECT *
FROM (SELECT *
FROM (SELECT inspeccion.sociedad_id,
inspeccion_id,
estado_id,
(SELECT des_estado
FROM estado
WHERE estado.estado_id = inspeccion.estado_id)
des_estado,
(SELECT numero_secuencia
FROM estado
WHERE estado.estado_id = inspeccion.estado_id)
numero_secuencia,
planta_id,
(SELECT des_planta
FROM planta
WHERE planta.planta_id = inspeccion.planta_id)
des_planta,
area_id,
(SELECT des_area
FROM area
WHERE area.area_id = inspeccion.area_id) des_area
,
igp_id,
'-'
nom_lider,
nom_inspector,
nom_responsable,
To_char(fecha_inspeccion, 'DD/MM/YYYY')
fecha_inspeccion,
observacion,
recomendacion
FROM inspeccion) inspeccion) t_inspeccion
WHERE estado_id NOT IN ( 10 )
AND t_inspeccion.estado_id IN ( 11, 12 )
AND t_inspeccion.planta_id = 42
AND t_inspeccion.fecha_inspeccion >= To_date('01/11/2010', 'dd/mm/yyyy')
AND t_inspeccion.sociedad_id = '0101'
ORDER BY t_inspeccion.numero_secuencia,
t_inspeccion.fecha_inspeccion
We were suspecting of the To_date('01/11/2010', 'dd/mm/yyyy')statement, but when we run the query trough DBVisualizer we have no trouble at all.
我们怀疑该To_date('01/11/2010', 'dd/mm/yyyy')语句，但是当我们通过 DBVisualizer 运行查询时，我们完全没有问题。
I'm kind of lost here. Maybe there's a DB Setting that's messing with the query, or some SAP Netweaver configuration? Or perhaps some Oracle Driver issue?
我有点迷失在这里。也许有一个数据库设置混淆了查询，或者一些 SAP Netweaver 配置？或者也许是一些 Oracle 驱动程序问题？
回答by Alex Poole
In the nested select that becomes t_inspeccionyou have:
在成为t_inspeccion您的嵌套选择中：
To_char(fecha_inspeccion, 'DD/MM/YYYY') fecha_inspeccion,
... and then in the outer whereclause you have:
...然后在外部where条款中你有：
AND t_inspeccion.fecha_inspeccion >= To_date('01/11/2010', 'dd/mm/yyyy')
Assuming the original inspection.fecha_inspeccionis a date, you seem to be converting to to a string, then doing an implicit conversion back to a date to compare it to your fixed value. I suspect the implicit conversion of t_inspeccion.fecha_inspeccionis throwing the error, possibly from an unexpected NLS_DATE_FORMATsetting; which in Java is normally obtained from your locale. From the error I'd guess it's implicitly using MM/DD/YYYY, but could easily be something else.
假设原始inspection.fecha_inspeccion是 a date，您似乎正在转换为字符串，然后进行隐式转换回日期以将其与您的固定值进行比较。我怀疑 的隐式转换t_inspeccion.fecha_inspeccion正在抛出错误，可能来自意外NLS_DATE_FORMAT设置；它在 Java 中通常是从您的语言环境中获得的。从错误中我猜它是隐式使用 MM/DD/YYYY，但很容易是别的东西。
I don't see why you had the to_char()at all though, unless you particularly want the text format in the results; in which case you need to either explicitly convert the date back in the whereclause:
我不明白为什么你有to_char()，除非你特别想要结果中的文本格式；在这种情况下，您需要在where子句中明确地将日期转换回：
AND To_date(t_inspeccion.fecha_inspeccion, 'dd/mm/yyyy')
>= To_date('01/11/2010', 'dd/mm/yyyy')
... or pull the original date column as well and compare using that; though that would perhaps need you to list all the other columns in the outermost select. If you are, or expect to be, using an index on fecha_inspeccionthen you may also be degrading performance by treating it as a string.
...或者也拉出原始日期列并使用它进行比较；尽管这可能需要您在最外层列出所有其他列select。如果您正在或希望使用索引，fecha_inspeccion那么您也可能将其视为字符串来降低性能。
