[jira] Created: (OFBIZ-3305) ORA-01843: not a valid month while using oracle database

[Nicolas Malin (Jira)]

ORA-01843: not a valid month while using oracle database
--------------------------------------------------------

                 Key: OFBIZ-3305
                 URL: https://issues.apache.org/jira/browse/OFBIZ-3305
             Project: OFBiz
          Issue Type: Bug
          Components: framework
    Affects Versions: Release Branch 9.04
            Reporter: Nathan Liang


ORA-01843: not a valid month while using oracle database



I used oracle 10g as the backend database of OFBiz 9.04 and got ORA-01843 error.

Here are my oracle instance's locale setting:
SELECT name,value$ from sys.props$ where name like '%NLS%';
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
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
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0


Datetime used in the generated sql should like to_date('2009-12-04 17:40:45.734', 'yyyy-mm-dd hh24:mi:ss') instead of literal string '2009-12-04 17:40:45.734'
Also I found this error keep throwing out and my log file increasing quickly, I got a 2G bytes file in 40 minutes:
                                                                     
2009-12-04  18:19     2,039,242,232 console.log                                                                                    
2009-12-04  18:19        10,140,093 debug.log                                                                                      
2009-12-04  18:06        10,240,075 debug.log.1                                                                                    



Error details:

2009-12-04 18:19:47,984 (org.ofbiz.service.job.JobPoller@1d38b87) [         JobManager.java:187:ERROR]
---- exception report ----------------------------------------------------------
Error in polling JobSandbox: [org.ofbiz.entity.GenericDataSourceException: Generic Entity Exception occured in updateByCondition (SQL Exception while executing the following:UPDATE OFBIZ.JOB_SANDBOX SET RUN_BY_INSTANCE_ID = ?, STATUS_ID = ? WHERE ((RUN_TIME <= '2009-12-04 17:40:45.734' AND START_DATE_TIME IS NULL AND CANCEL_DATE_TIME IS NULL AND RUN_BY_INSTANCE_ID IS NULL) AND (POOL_ID IS NULL OR POOL_ID = 'pool')) (ORA-01843: not a valid month
))]. Rolling back transaction.
Exception: org.ofbiz.entity.GenericDataSourceException
Message: Generic Entity Exception occured in updateByCondition (SQL Exception while executing the following:UPDATE OFBIZ.JOB_SANDBOX SET RUN_BY_INSTANCE_ID = ?, STATUS_ID = ? WHERE ((RUN_TIME <= '2009-12-04 17:40:45.734' AND START_DATE_TIME IS NULL AND CANCEL_DATE_TIME IS NULL AND RUN_BY_INSTANCE_ID IS NULL) AND (POOL_ID IS NULL OR POOL_ID = 'pool')) (ORA-01843: not a valid month
))
---- cause ---------------------------------------------------------------------
Exception: org.ofbiz.entity.GenericDataSourceException
Message: SQL Exception while executing the following:UPDATE OFBIZ.JOB_SANDBOX SET RUN_BY_INSTANCE_ID = ?, STATUS_ID = ? WHERE ((RUN_TIME <= '2009-12-04 17:40:45.734' AND START_DATE_TIME IS NULL AND CANCEL_DATE_TIME IS NULL AND RUN_BY_INSTANCE_ID IS NULL) AND (POOL_ID IS NULL OR POOL_ID = 'pool')) (ORA-01843: not a valid month
)
---- cause ---------------------------------------------------------------------
Exception: java.sql.SQLException
Message: ORA-01843: not a valid month

---- stack trace ---------------------------------------------------------------
java.sql.SQLException: ORA-01843: not a valid month
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
org.ofbiz.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:429)
org.ofbiz.entity.datasource.GenericDAO.updateByCondition(GenericDAO.java:329)
org.ofbiz.entity.datasource.GenericDAO.updateByCondition(GenericDAO.java:288)
org.ofbiz.entity.datasource.GenericHelperDAO.storeByCondition(GenericHelperDAO.java:186)
org.ofbiz.entity.GenericDelegator.storeByCondition(GenericDelegator.java:1276)
org.ofbiz.entity.GenericDelegator.storeByCondition(GenericDelegator.java:1246)
org.ofbiz.service.job.JobManager.poll(JobManager.java:157)
org.ofbiz.service.job.JobPoller.run(JobPoller.java:90)
java.lang.Thread.run(Thread.java:619)
--------------------------------------------------------------------------------
 

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

[Nicolas Malin (Jira)]

     [ https://issues.apache.org/jira/browse/OFBIZ-3305?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Adrian Crum closed OFBIZ-3305.
------------------------------

    Resolution: Invalid

This type of question is best asked on the user mailing list.

This is not a bug. OFBiz creates ANSI-SQL, not Oracle-specific SQL. It appears you need to change your database's locale settings so that it will accept/parse a date string.

> ORA-01843: not a valid month while using oracle database
> --------------------------------------------------------
>
>                 Key: OFBIZ-3305
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-3305
>             Project: OFBiz
>          Issue Type: Bug
>          Components: framework
>    Affects Versions: Release Branch 9.04
>            Reporter: Nathan Liang
>
> ORA-01843: not a valid month while using oracle database
> I used oracle 10g as the backend database of OFBiz 9.04 and got ORA-01843 error.
> Here are my oracle instance's locale setting:
> SELECT name,value$ from sys.props$ where name like '%NLS%';
> NLS_LANGUAGE AMERICAN
> NLS_TERRITORY AMERICA
> NLS_CURRENCY $
> NLS_ISO_CURRENCY AMERICA
> NLS_NUMERIC_CHARACTERS .,
> NLS_CHARACTERSET AL32UTF8
> NLS_CALENDAR GREGORIAN
> NLS_DATE_FORMAT DD-MON-RR
> NLS_DATE_LANGUAGE AMERICAN
> NLS_SORT BINARY
> 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
> NLS_LENGTH_SEMANTICS BYTE
> NLS_NCHAR_CONV_EXCP FALSE
> NLS_NCHAR_CHARACTERSET AL16UTF16
> NLS_RDBMS_VERSION 10.2.0.1.0
> Datetime used in the generated sql should like to_date('2009-12-04 17:40:45.734', 'yyyy-mm-dd hh24:mi:ss') instead of literal string '2009-12-04 17:40:45.734'
> Also I found this error keep throwing out and my log file increasing quickly, I got a 2G bytes file in 40 minutes:
>                                                                      
> 2009-12-04  18:19     2,039,242,232 console.log                                                                                    
> 2009-12-04  18:19        10,140,093 debug.log                                                                                      
> 2009-12-04  18:06        10,240,075 debug.log.1                                                                                    
> Error details:
> 2009-12-04 18:19:47,984 (org.ofbiz.service.job.JobPoller@1d38b87) [         JobManager.java:187:ERROR]
> ---- exception report ----------------------------------------------------------
> Error in polling JobSandbox: [org.ofbiz.entity.GenericDataSourceException: Generic Entity Exception occured in updateByCondition (SQL Exception while executing the following:UPDATE OFBIZ.JOB_SANDBOX SET RUN_BY_INSTANCE_ID = ?, STATUS_ID = ? WHERE ((RUN_TIME <= '2009-12-04 17:40:45.734' AND START_DATE_TIME IS NULL AND CANCEL_DATE_TIME IS NULL AND RUN_BY_INSTANCE_ID IS NULL) AND (POOL_ID IS NULL OR POOL_ID = 'pool')) (ORA-01843: not a valid month
> ))]. Rolling back transaction.
> Exception: org.ofbiz.entity.GenericDataSourceException
> Message: Generic Entity Exception occured in updateByCondition (SQL Exception while executing the following:UPDATE OFBIZ.JOB_SANDBOX SET RUN_BY_INSTANCE_ID = ?, STATUS_ID = ? WHERE ((RUN_TIME <= '2009-12-04 17:40:45.734' AND START_DATE_TIME IS NULL AND CANCEL_DATE_TIME IS NULL AND RUN_BY_INSTANCE_ID IS NULL) AND (POOL_ID IS NULL OR POOL_ID = 'pool')) (ORA-01843: not a valid month
> ))
> ---- cause ---------------------------------------------------------------------
> Exception: org.ofbiz.entity.GenericDataSourceException
> Message: SQL Exception while executing the following:UPDATE OFBIZ.JOB_SANDBOX SET RUN_BY_INSTANCE_ID = ?, STATUS_ID = ? WHERE ((RUN_TIME <= '2009-12-04 17:40:45.734' AND START_DATE_TIME IS NULL AND CANCEL_DATE_TIME IS NULL AND RUN_BY_INSTANCE_ID IS NULL) AND (POOL_ID IS NULL OR POOL_ID = 'pool')) (ORA-01843: not a valid month
> )
> ---- cause ---------------------------------------------------------------------
> Exception: java.sql.SQLException
> Message: ORA-01843: not a valid month
> ---- stack trace ---------------------------------------------------------------
> java.sql.SQLException: ORA-01843: not a valid month
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
> oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
> oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
> oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
> oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
> oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
> oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
> oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
> org.ofbiz.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:429)
> org.ofbiz.entity.datasource.GenericDAO.updateByCondition(GenericDAO.java:329)
> org.ofbiz.entity.datasource.GenericDAO.updateByCondition(GenericDAO.java:288)
> org.ofbiz.entity.datasource.GenericHelperDAO.storeByCondition(GenericHelperDAO.java:186)
> org.ofbiz.entity.GenericDelegator.storeByCondition(GenericDelegator.java:1276)
> org.ofbiz.entity.GenericDelegator.storeByCondition(GenericDelegator.java:1246)
> org.ofbiz.service.job.JobManager.poll(JobManager.java:157)
> org.ofbiz.service.job.JobPoller.run(JobPoller.java:90)
> java.lang.Thread.run(Thread.java:619)
> --------------------------------------------------------------------------------
>  

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.