Bidisha Bidisha - 5 months ago 45
Java Question

ORA-01843: not a valid month -java to sql date conversion

I have the requirement to insert specific timestamp from java to Oracle database;
My oracle column ChangeTime is of DATA_TYPE=TIMESTAMP(6) and expects value in the format - 08-APR-16 09.52.22.000000000 AM.

I have Converted the java.sql.Timestamp to string in my java code :

String changeTimestamp = new Timestamp(System.currentTimeMillis()).toString();


and my SQL PreparedStatement is as below :

<property name="updatePersonDetail">
<bean class="com.cigna.ibor.dao.UpdateWithParameters" parent="dao.template">
<constructor-arg ref="mydb.ds" />
<constructor-arg>
<value>
<![CDATA[
UPDATE MYDB.INDIV_DETAIL
SET PERSON_ID =?
, LAST_OPER_ID = ?
, LAST_TIMESTMP = ?,

WHERE SRC_LOC_CD = ?
AND SRC_SYS_ID = ?
]]>
</value>
</constructor-arg>
<constructor-arg>
<list>
<ref local="java.sql.Types.VARCHAR" />
<ref local="java.sql.Types.VARCHAR" />
<ref local="java.sql.Types.VARCHAR" />
<ref local="java.sql.Types.VARCHAR" />
<ref local="java.sql.Types.VARCHAR" />

</list>
</constructor-arg>
</bean>
</property>


I am getting java.sql.SQLDataException: ORA-01843: not a valid month when running.
Any help is appreciated !

Answer

If you have a string formatted like 08-APR-16 09.52.22.000000000 AM then you can specify the format instead of relying on implicit conversion:

UPDATE MYDB.INDIV_DETAIL
SET   PERSON_ID =?
    , LAST_OPER_ID = ?
    , LAST_TIMESTMP = TO_TIMESTAMP(?, 'DD-MON-RR HH:MI:SS.FF9 AM'),
WHERE SRC_LOC_CD = ?
  AND SRC_SYS_ID = ?

The format model has to match what you are actually passing. It sounds like you're assuming that is the session's NLS_TIMESTAMP_FORMAT setting but the error suggests that is not the case. Don't confuse how your client/IDE displays a queried date/timestamp with how it's stored - there is no intrinsic format, Oracle uses an internal representation that is nothing like the displayed value.

Even with this you are assuming the Oracle session's data language matches the Java value - that they both use English month names, for example. If you aren't sure what the database session's date language is but know the Java string is always English you can specify that in the conversion:

    , LAST_TIMESTMP = TO_TIMESTAMP(?, 'DD-MON-RR HH:MI:SS.FF9 AM'
      , 'NLS_DATE_LANGUAGE=ENGLISH'),

If you have a Java timestamp value are are current converting it to a string in that format to make this call, you can pass it as a timestamp rather than converting it to and from a string.

Comments