java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Hibernate JIRA | Shawn Clowater | 1 decade ago
  1. 0

    When combining a filter at a top level class with a criteria paramater (positional) it was possible that the parameters would be bound in the wrong order. i.e. the following filter at the class level <filter name="versionFilter" condition="((:effectiveDate BETWEEN CREATE_DATE AND CREATE_DATE OR :inactiveDate BETWEEN CREATE_DATE AND CREATE_DATE))"/> <filter-def name="versionFilter"> <filter-param name="effectiveDate" type="java.util.Date"/> <filter-param name="inactiveDate" type="java.util.Date"/> </filter-def> combined with a simple criteria parameter results in DEBUG [AWT-EventQueue-0] (NullableType.java:46) - binding '2004-08-01 00:00:00' to parameter: 1 DEBUG [AWT-EventQueue-0] (NullableType.java:46) - binding '2004-08-31 23:59:59' to parameter: 2 DEBUG [AWT-EventQueue-0] (NullableType.java:46) - binding '4' to parameter: 3 DEBUG [AWT-EventQueue-0] (AbstractBatcher.java:260) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1) DEBUG [AWT-EventQueue-0] (AbstractBatcher.java:361) - closing statement DEBUG [AWT-EventQueue-0] (JDBCExceptionReporter.java:50) - could not execute query [select this_.AGMT_CNTRCT_LINE_ID as AGMT_CNT1_3_, this_.AGMT_CNTRCT_ID as AGMT_CNT2_18_3_, this_.NOTES_ID as NOTES_ID18_3_, this_.UPDATE_DATE as UPDATE_D4_18_3_, this_.CREATE_DATE as CREATE_D5_18_3_, this_.UPDATE_USER as UPDATE_U6_18_3_, this_.CREATE_USER as CREATE_U7_18_3_, agmtcontra_.AGMT_CNTRCT_ID as AGMT_CNT1_0_, agmtcontra_.AGMT_ID as AGMT_ID16_0_, agmtcontra_.NOTES_ID as NOTES_ID16_0_, agmtcontra_.UPDATE_DATE as UPDATE_D4_16_0_, agmtcontra_.CREATE_DATE as CREATE_D5_16_0_, agmtcontra_.UPDATE_USER as UPDATE_U6_16_0_, agmtcontra_.CREATE_USER as CREATE_U7_16_0_, acversionj_.AGMT_CNTRCT_V_ID as AGMT_CNT1_1_, acversionj_.AGMT_CNTRCT_ID as AGMT_CNT2_17_1_, acversionj_.CNTRCT_SEQ as CNTRCT_SEQ17_1_, acversionj_.CNTRCT_REVISION as CNTRCT_R4_17_1_, acversionj_.AGMT_CNTRCT_TYPE_ID as AGMT_CNT5_17_1_, acversionj_.CNTRCT_REF as CNTRCT_REF17_1_, acversionj_.DESCRIPTION as DESCRIPT7_17_1_, acversionj_.QTY_VOLUME_TYPE_CODE as QTY_VOLU8_17_1_, acversionj_.QTY_LIMIT_FLG as QTY_LIMI9_17_1_, acversionj_.EFFECTIVE_DATE as EFFECTI10_17_1_, acversionj_.INACTIVE_DATE as INACTIV11_17_1_, acversionj_.UPDATE_DATE as UPDATE_12_17_1_, acversionj_.CREATE_DATE as CREATE_13_17_1_, acversionj_.UPDATE_USER as UPDATE_14_17_1_, acversionj_.CREATE_USER as CREATE_15_17_1_, acversionj_.ACTIVE_FLG as ACTIVE_FLG17_1_, aclversion_.AGMT_CNTRCT_LINE_V_ID as AGMT_CNT1_2_, aclversion_.AGMT_CNTRCT_LINE_ID as AGMT_CNT2_23_2_, aclversion_.QTY_LIMIT_ENFORCED_FLG as QTY_LIMI3_23_2_, aclversion_.DESCRIPTION as DESCRIPT4_23_2_, aclversion_.BUYER_STKHLDR_ID as BUYER_ST5_23_2_, aclversion_.SELLER_STKHLDR_ID as SELLER_S6_23_2_, aclversion_.SHIPPER_STKHLDR_ID as SHIPPER_7_23_2_, aclversion_.SRC_ADDR_ID as SRC_ADDR8_23_2_, aclversion_.SRC_INVENTORY_ID as SRC_INVE9_23_2_, aclversion_.DEST_ADDR_ID as DEST_AD10_23_2_, aclversion_.DEST_INVENTORY_ID as DEST_IN11_23_2_, aclversion_.DELIVERY_POINT_ADDR_ID as DELIVER12_23_2_, aclversion_.TITLE_TRANSFER_ADDR_ID as TITLE_T13_23_2_, aclversion_.SHIP_MODE_CODE as SHIP_MO14_23_2_, aclversion_.SHIP_TERMS as SHIP_TERMS23_2_, aclversion_.PRIORITY as PRIORITY23_2_, aclversion_.END_USE_CODE as END_USE17_23_2_, aclversion_.QTY_CALC_METHOD as QTY_CAL18_23_2_, aclversion_.NOMINATION_TYPE as NOMINAT19_23_2_, aclversion_.BEFORE_AFTER_IND as BEFORE_20_23_2_, aclversion_.IN_TRANSIT_FLG as IN_TRAN21_23_2_, aclversion_.ROUTING_ID as ROUTING_ID23_2_, aclversion_.EXCHANGE_PROCESS_FLG as EXCHANG23_23_2_, aclversion_.BACKFLOW_FLG as BACKFLO24_23_2_, aclversion_.COSTING_SRC_FLG as COSTING25_23_2_, aclversion_.DEDUCTION_LIMIT as DEDUCTI26_23_2_, aclversion_.QTY as QTY23_2_, aclversion_.QTY_UOM_CODE as QTY_UOM28_23_2_, aclversion_.ENERGY as ENERGY23_2_, aclversion_.ENERGY_UOM_CODE as ENERGY_30_23_2_, aclversion_.QTY_GAIN_LOSS_INDEX_PRICE_ID as QTY_GAI31_23_2_, aclversion_.EFFECTIVE_DATE as EFFECTI32_23_2_, aclversion_.INACTIVE_DATE as INACTIV33_23_2_, aclversion_.UPDATE_DATE as UPDATE_34_23_2_, aclversion_.CREATE_DATE as CREATE_35_23_2_, aclversion_.UPDATE_USER as UPDATE_36_23_2_, aclversion_.CREATE_USER as CREATE_37_23_2_, aclversion_.ACTIVE_FLG as ACTIVE_FLG23_2_ from AGMT_CNTRCT_LINE this_ inner join AGMT_CNTRCT agmtcontra_ on this_.AGMT_CNTRCT_ID=agmtcontra_.AGMT_CNTRCT_ID inner join AGMT_CNTRCT_V acversionj_ on agmtcontra_.AGMT_CNTRCT_ID=acversionj_.AGMT_CNTRCT_ID inner join AGMT_CNTRCT_LINE_V aclversion_ on this_.AGMT_CNTRCT_LINE_ID=aclversion_.AGMT_CNTRCT_LINE_ID where acversionj_.AGMT_CNTRCT_TYPE_ID=? and ((:versionFilter.effectiveDate BETWEEN this_.CREATE_DATE AND this_.CREATE_DATE OR :versionFilter.inactiveDate BETWEEN this_.CREATE_DATE AND this_.CREATE_DATE))] java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got DATE at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:103) at org.hibernate.loader.Loader.getResultSet(Loader.java:1065) at org.hibernate.loader.Loader.doQuery(Loader.java:344) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:190) at org.hibernate.loader.Loader.doList(Loader.java:1271) at org.hibernate.loader.Loader.list(Loader.java:1254) at org.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:169) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:2100) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:274) at com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:340) at com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:315) at com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:281) at com.entero.enterovision.service.agmt.AgmtService.fetchAgmtContractLines(AgmtService.java:488) at com.entero.enterovision.components.agreementitemprice.AgreementController.find(AgreementController.java:71) at com.entero.enterovision.components.AbstractController.findByParameters(AbstractController.java:32) at com.entero.enterovision.components.ActiveWorkbench.populate(ActiveWorkbench.java:329) at com.entero.enterovision.components.AbstractPMAWorkbench.populate(AbstractPMAWorkbench.java:30) at com.entero.enterovision.components.AbstractSidebarSearchPanel.populateWorkbench(AbstractSidebarSearchPanel.java:241) at com.entero.enterovision.components.agreementitemprice.AgreementItemPriceSearchSidebar.find(AgreementItemPriceSearchSidebar.java:203) at com.entero.infrastructure.ui.sidebar.SidebarPanel.executeFunction(SidebarPanel.java:158) at com.entero.enterovision.components.AbstractSidebarSearchPanel.search(AbstractSidebarSearchPanel.java:183) at com.entero.enterovision.components.AbstractSidebarSearchPanel.access$000(AbstractSidebarSearchPanel.java:32) at com.entero.enterovision.components.AbstractSidebarSearchPanel$2.actionPerformed(AbstractSidebarSearchPanel.java:112) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786) at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245) at java.awt.Component.processMouseEvent(Component.java:5100) at java.awt.Component.processEvent(Component.java:4897) at java.awt.Container.processEvent(Container.java:1569) at java.awt.Component.dispatchEventImpl(Component.java:3615) at java.awt.Container.dispatchEventImpl(Container.java:1627) at java.awt.Component.dispatchEvent(Component.java:3477) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:3483) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128) at java.awt.Container.dispatchEventImpl(Container.java:1613) at java.awt.Window.dispatchEventImpl(Window.java:1606) at java.awt.Component.dispatchEvent(Component.java:3477) at java.awt.EventQueue.dispatchEvent(EventQueue.java:456) at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137) at java.awt.EventDispatchThread.run(EventDispatchThread.java:100) WARN [AWT-EventQueue-0] (JDBCExceptionReporter.java:58) - SQL Error: 932, SQLState: 42000 ERROR [AWT-EventQueue-0] (JDBCExceptionReporter.java:59) - ORA-00932: inconsistent datatypes: expected NUMBER got DATE ----- Note how the parameters were applied in the wrong order. This was due to the Loader class in the processFilterParameters() method ALWAYS putting the filter parameters at the front of the queue. It is even more pronounced if you have additional filters further on down the chain.

    Hibernate JIRA | 1 decade ago | Shawn Clowater
    java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got DATE
  2. 0

    When combining a filter at a top level class with a criteria paramater (positional) it was possible that the parameters would be bound in the wrong order. i.e. the following filter at the class level <filter name="versionFilter" condition="((:effectiveDate BETWEEN CREATE_DATE AND CREATE_DATE OR :inactiveDate BETWEEN CREATE_DATE AND CREATE_DATE))"/> <filter-def name="versionFilter"> <filter-param name="effectiveDate" type="java.util.Date"/> <filter-param name="inactiveDate" type="java.util.Date"/> </filter-def> combined with a simple criteria parameter results in DEBUG [AWT-EventQueue-0] (NullableType.java:46) - binding '2004-08-01 00:00:00' to parameter: 1 DEBUG [AWT-EventQueue-0] (NullableType.java:46) - binding '2004-08-31 23:59:59' to parameter: 2 DEBUG [AWT-EventQueue-0] (NullableType.java:46) - binding '4' to parameter: 3 DEBUG [AWT-EventQueue-0] (AbstractBatcher.java:260) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1) DEBUG [AWT-EventQueue-0] (AbstractBatcher.java:361) - closing statement DEBUG [AWT-EventQueue-0] (JDBCExceptionReporter.java:50) - could not execute query [select this_.AGMT_CNTRCT_LINE_ID as AGMT_CNT1_3_, this_.AGMT_CNTRCT_ID as AGMT_CNT2_18_3_, this_.NOTES_ID as NOTES_ID18_3_, this_.UPDATE_DATE as UPDATE_D4_18_3_, this_.CREATE_DATE as CREATE_D5_18_3_, this_.UPDATE_USER as UPDATE_U6_18_3_, this_.CREATE_USER as CREATE_U7_18_3_, agmtcontra_.AGMT_CNTRCT_ID as AGMT_CNT1_0_, agmtcontra_.AGMT_ID as AGMT_ID16_0_, agmtcontra_.NOTES_ID as NOTES_ID16_0_, agmtcontra_.UPDATE_DATE as UPDATE_D4_16_0_, agmtcontra_.CREATE_DATE as CREATE_D5_16_0_, agmtcontra_.UPDATE_USER as UPDATE_U6_16_0_, agmtcontra_.CREATE_USER as CREATE_U7_16_0_, acversionj_.AGMT_CNTRCT_V_ID as AGMT_CNT1_1_, acversionj_.AGMT_CNTRCT_ID as AGMT_CNT2_17_1_, acversionj_.CNTRCT_SEQ as CNTRCT_SEQ17_1_, acversionj_.CNTRCT_REVISION as CNTRCT_R4_17_1_, acversionj_.AGMT_CNTRCT_TYPE_ID as AGMT_CNT5_17_1_, acversionj_.CNTRCT_REF as CNTRCT_REF17_1_, acversionj_.DESCRIPTION as DESCRIPT7_17_1_, acversionj_.QTY_VOLUME_TYPE_CODE as QTY_VOLU8_17_1_, acversionj_.QTY_LIMIT_FLG as QTY_LIMI9_17_1_, acversionj_.EFFECTIVE_DATE as EFFECTI10_17_1_, acversionj_.INACTIVE_DATE as INACTIV11_17_1_, acversionj_.UPDATE_DATE as UPDATE_12_17_1_, acversionj_.CREATE_DATE as CREATE_13_17_1_, acversionj_.UPDATE_USER as UPDATE_14_17_1_, acversionj_.CREATE_USER as CREATE_15_17_1_, acversionj_.ACTIVE_FLG as ACTIVE_FLG17_1_, aclversion_.AGMT_CNTRCT_LINE_V_ID as AGMT_CNT1_2_, aclversion_.AGMT_CNTRCT_LINE_ID as AGMT_CNT2_23_2_, aclversion_.QTY_LIMIT_ENFORCED_FLG as QTY_LIMI3_23_2_, aclversion_.DESCRIPTION as DESCRIPT4_23_2_, aclversion_.BUYER_STKHLDR_ID as BUYER_ST5_23_2_, aclversion_.SELLER_STKHLDR_ID as SELLER_S6_23_2_, aclversion_.SHIPPER_STKHLDR_ID as SHIPPER_7_23_2_, aclversion_.SRC_ADDR_ID as SRC_ADDR8_23_2_, aclversion_.SRC_INVENTORY_ID as SRC_INVE9_23_2_, aclversion_.DEST_ADDR_ID as DEST_AD10_23_2_, aclversion_.DEST_INVENTORY_ID as DEST_IN11_23_2_, aclversion_.DELIVERY_POINT_ADDR_ID as DELIVER12_23_2_, aclversion_.TITLE_TRANSFER_ADDR_ID as TITLE_T13_23_2_, aclversion_.SHIP_MODE_CODE as SHIP_MO14_23_2_, aclversion_.SHIP_TERMS as SHIP_TERMS23_2_, aclversion_.PRIORITY as PRIORITY23_2_, aclversion_.END_USE_CODE as END_USE17_23_2_, aclversion_.QTY_CALC_METHOD as QTY_CAL18_23_2_, aclversion_.NOMINATION_TYPE as NOMINAT19_23_2_, aclversion_.BEFORE_AFTER_IND as BEFORE_20_23_2_, aclversion_.IN_TRANSIT_FLG as IN_TRAN21_23_2_, aclversion_.ROUTING_ID as ROUTING_ID23_2_, aclversion_.EXCHANGE_PROCESS_FLG as EXCHANG23_23_2_, aclversion_.BACKFLOW_FLG as BACKFLO24_23_2_, aclversion_.COSTING_SRC_FLG as COSTING25_23_2_, aclversion_.DEDUCTION_LIMIT as DEDUCTI26_23_2_, aclversion_.QTY as QTY23_2_, aclversion_.QTY_UOM_CODE as QTY_UOM28_23_2_, aclversion_.ENERGY as ENERGY23_2_, aclversion_.ENERGY_UOM_CODE as ENERGY_30_23_2_, aclversion_.QTY_GAIN_LOSS_INDEX_PRICE_ID as QTY_GAI31_23_2_, aclversion_.EFFECTIVE_DATE as EFFECTI32_23_2_, aclversion_.INACTIVE_DATE as INACTIV33_23_2_, aclversion_.UPDATE_DATE as UPDATE_34_23_2_, aclversion_.CREATE_DATE as CREATE_35_23_2_, aclversion_.UPDATE_USER as UPDATE_36_23_2_, aclversion_.CREATE_USER as CREATE_37_23_2_, aclversion_.ACTIVE_FLG as ACTIVE_FLG23_2_ from AGMT_CNTRCT_LINE this_ inner join AGMT_CNTRCT agmtcontra_ on this_.AGMT_CNTRCT_ID=agmtcontra_.AGMT_CNTRCT_ID inner join AGMT_CNTRCT_V acversionj_ on agmtcontra_.AGMT_CNTRCT_ID=acversionj_.AGMT_CNTRCT_ID inner join AGMT_CNTRCT_LINE_V aclversion_ on this_.AGMT_CNTRCT_LINE_ID=aclversion_.AGMT_CNTRCT_LINE_ID where acversionj_.AGMT_CNTRCT_TYPE_ID=? and ((:versionFilter.effectiveDate BETWEEN this_.CREATE_DATE AND this_.CREATE_DATE OR :versionFilter.inactiveDate BETWEEN this_.CREATE_DATE AND this_.CREATE_DATE))] java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got DATE at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:103) at org.hibernate.loader.Loader.getResultSet(Loader.java:1065) at org.hibernate.loader.Loader.doQuery(Loader.java:344) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:190) at org.hibernate.loader.Loader.doList(Loader.java:1271) at org.hibernate.loader.Loader.list(Loader.java:1254) at org.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:169) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:2100) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:274) at com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:340) at com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:315) at com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:281) at com.entero.enterovision.service.agmt.AgmtService.fetchAgmtContractLines(AgmtService.java:488) at com.entero.enterovision.components.agreementitemprice.AgreementController.find(AgreementController.java:71) at com.entero.enterovision.components.AbstractController.findByParameters(AbstractController.java:32) at com.entero.enterovision.components.ActiveWorkbench.populate(ActiveWorkbench.java:329) at com.entero.enterovision.components.AbstractPMAWorkbench.populate(AbstractPMAWorkbench.java:30) at com.entero.enterovision.components.AbstractSidebarSearchPanel.populateWorkbench(AbstractSidebarSearchPanel.java:241) at com.entero.enterovision.components.agreementitemprice.AgreementItemPriceSearchSidebar.find(AgreementItemPriceSearchSidebar.java:203) at com.entero.infrastructure.ui.sidebar.SidebarPanel.executeFunction(SidebarPanel.java:158) at com.entero.enterovision.components.AbstractSidebarSearchPanel.search(AbstractSidebarSearchPanel.java:183) at com.entero.enterovision.components.AbstractSidebarSearchPanel.access$000(AbstractSidebarSearchPanel.java:32) at com.entero.enterovision.components.AbstractSidebarSearchPanel$2.actionPerformed(AbstractSidebarSearchPanel.java:112) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786) at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245) at java.awt.Component.processMouseEvent(Component.java:5100) at java.awt.Component.processEvent(Component.java:4897) at java.awt.Container.processEvent(Container.java:1569) at java.awt.Component.dispatchEventImpl(Component.java:3615) at java.awt.Container.dispatchEventImpl(Container.java:1627) at java.awt.Component.dispatchEvent(Component.java:3477) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:3483) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128) at java.awt.Container.dispatchEventImpl(Container.java:1613) at java.awt.Window.dispatchEventImpl(Window.java:1606) at java.awt.Component.dispatchEvent(Component.java:3477) at java.awt.EventQueue.dispatchEvent(EventQueue.java:456) at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137) at java.awt.EventDispatchThread.run(EventDispatchThread.java:100) WARN [AWT-EventQueue-0] (JDBCExceptionReporter.java:58) - SQL Error: 932, SQLState: 42000 ERROR [AWT-EventQueue-0] (JDBCExceptionReporter.java:59) - ORA-00932: inconsistent datatypes: expected NUMBER got DATE ----- Note how the parameters were applied in the wrong order. This was due to the Loader class in the processFilterParameters() method ALWAYS putting the filter parameters at the front of the queue. It is even more pronounced if you have additional filters further on down the chain.

    Hibernate JIRA | 1 decade ago | Shawn Clowater
    java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got DATE
  3. 0

    using JTA and JDBC in a servlet, IBM Websphere 5.0.2

    Google Groups | 1 decade ago | Anony
    java.sql.SQLException: ORA-00933: SQL command not properly ended
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    After roughly a week and half of painful debugging, I figured out the problem with hibernate generating only a "PARTIAL QUERY" for the below mentioned code. (Right at the end of this post) The query generated was: "SELECT FROM" The query I was expecting: select catpersist0_.CAT_ID as CAT_ID, catpersist0_.NAME as NAME0_, catpersist0_.sex as sex0_, catpersist0_.weight as weight0_ from Cat catpersist0_ The problem is hibernate is not able to pick up the Persister class for the dynamic class mentioned in the mapping xml. Though the HashMap of Persisters class Mapping are well populated in the SessionFactoryImpl class constructor, there seems to be a problem while looking up or while retrieving persister class object from the same Map object. The look up process in my scenario starts from the getEntityPersisterUsingImports (String className)' method of the QueryTranslatorImpl class. While building the Persisters class mapping inside the SessionFactoryImpl class , entity name, used for the dynamic class element in the mapping XML is used as the key and an instance of the persister class is used as the value for the Map entry. While looking up the Persister class object for a given entity name from the FromParser.java 's token() method, FromParser uses getEntityPersisterUsingImports(token) method of the QueryTranslatorImpl.java class. The getEntityPersisterUsingImports method in the QueryTranslatorImpl.java class, in turn retrieves the the actual java Map implementation class name for the given entity-name from the mapping XML defined in the import element and uses the java Map class name as a parameter to the method, getEntityPersister(String) method. Which causes the SessionFactoryImpl class to return 'null' instead of a valid Persister class object. Since the 'classPersistersByName' Map object contain entity-name as the key, while the incoming parameter is a Map implementation class name to which entity-name maps to. This leads to incomplete generation of SQL in hibernate. Following is the patch that i applied in the org.hibernate.hql.QueryTranslatorImpl.java ****************************************************** Queryable getEntityPersisterUsingImports(String className) { final String importedClassName = factory.getImportedClassName(className); if (importedClassName==null) return null; try { //return ( Queryable ) factory.getEntityPersister(importedClassName); //### This is the buggy line ### return ( Queryable ) factory.getEntityPersister(className); } catch (MappingException me) { return null; } } ************************************************************* After the fix i ran few examples and some of my own programs which were previoulsy running/not-running both seem to work fine. I am surely interested to know about it. Please respond to the post or email to me at madhusudanmk@yahoo.com. Cheers, Madhusudan M Krishnamurthy Hibernate version:3.0 Mapping documents: <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping auto-import="false"> <import class="java.util.Map" rename="CatPersistentMap"/> <dynamic-class entity-name="CatPersistentMap" table="Cat" discriminator-value="C" persister="org.hibernate.persister.SingleTableEntityPersister"> <id name="id" type="string" unsaved-value="null" > <column name="CAT_ID" sql-type="char(32)" not-null="true"/> <generator class="uuid.hex"/> </id> <property name="name" type="java.lang.String"> <column name="NAME" length="16" not-null="true"/> </property> <property name="sex" type="char"/> <property name="weight" type="float" /> </dynamic-class> </hibernate-mapping> Code between sessionFactory.openSession() and session.close(): transaction = session.beginTransaction(); String hqlQuery = "from CatPersistentMap catPersistentMap"; Query query = session.createQuery(hqlQuery); System.out.println("SQL :: " + query.getQueryString()); List catList = query.list(); if (catList != null && !catList.isEmpty()) { Iterator catIterator = catList.iterator(); while (catIterator.hasNext()) { Map catMap = (Map) catIterator.next(); System.out.println("Cat Map :: " + catMap); } } else System.out.println("Empty records"); transaction.commit(); Full stack trace of any exception that occurs: java.sql.SQLException: ORA-00936: missing expression at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:96) at org.hibernate.loader.Loader.getResultSet(Loader.java:1044) at org.hibernate.loader.Loader.doQuery(Loader.java:321) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167) at org.hibernate.loader.Loader.doList(Loader.java:1201) at org.hibernate.loader.Loader.list(Loader.java:1186) at org.hibernate.hql.QueryTranslatorImpl.list(QueryTranslatorImpl.java:872) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:812) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:84) at com.accelrys.platform.test.TestCatQuery.executeCustomHQL(TestCatQuery.java:164) at com.accelrys.platform.test.TestCatQuery.main(TestCatQuery.java:53) SEVERE: Could not execute query java.sql.SQLException: ORA-00936: missing expression at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:96) at org.hibernate.loader.Loader.getResultSet(Loader.java:1044) at org.hibernate.loader.Loader.doQuery(Loader.java:321) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167) at org.hibernate.loader.Loader.doList(Loader.java:1201) at org.hibernate.loader.Loader.list(Loader.java:1186) at org.hibernate.hql.QueryTranslatorImpl.list(QueryTranslatorImpl.java:872) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:812) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:84) at com.accelrys.platform.test.TestCatQuery.executeCustomHQL(TestCatQuery.java:164) at com.accelrys.platform.test.TestCatQuery.main(TestCatQuery.java:53) Name and version of the database you are using: Oracle 9i The generated SQL (show_sql=true): select from

    Hibernate JIRA | 1 decade ago | Madhusudan M Krishnamurthy
    java.sql.SQLException: ORA-00936: missing expression
  6. 0

    After roughly a week and half of painful debugging, I figured out the problem with hibernate generating only a "PARTIAL QUERY" for the below mentioned code. (Right at the end of this post) The query generated was: "SELECT FROM" The query I was expecting: select catpersist0_.CAT_ID as CAT_ID, catpersist0_.NAME as NAME0_, catpersist0_.sex as sex0_, catpersist0_.weight as weight0_ from Cat catpersist0_ The problem is hibernate is not able to pick up the Persister class for the dynamic class mentioned in the mapping xml. Though the HashMap of Persisters class Mapping are well populated in the SessionFactoryImpl class constructor, there seems to be a problem while looking up or while retrieving persister class object from the same Map object. The look up process in my scenario starts from the getEntityPersisterUsingImports (String className)' method of the QueryTranslatorImpl class. While building the Persisters class mapping inside the SessionFactoryImpl class , entity name, used for the dynamic class element in the mapping XML is used as the key and an instance of the persister class is used as the value for the Map entry. While looking up the Persister class object for a given entity name from the FromParser.java 's token() method, FromParser uses getEntityPersisterUsingImports(token) method of the QueryTranslatorImpl.java class. The getEntityPersisterUsingImports method in the QueryTranslatorImpl.java class, in turn retrieves the the actual java Map implementation class name for the given entity-name from the mapping XML defined in the import element and uses the java Map class name as a parameter to the method, getEntityPersister(String) method. Which causes the SessionFactoryImpl class to return 'null' instead of a valid Persister class object. Since the 'classPersistersByName' Map object contain entity-name as the key, while the incoming parameter is a Map implementation class name to which entity-name maps to. This leads to incomplete generation of SQL in hibernate. Following is the patch that i applied in the org.hibernate.hql.QueryTranslatorImpl.java ****************************************************** Queryable getEntityPersisterUsingImports(String className) { final String importedClassName = factory.getImportedClassName(className); if (importedClassName==null) return null; try { //return ( Queryable ) factory.getEntityPersister(importedClassName); //### This is the buggy line ### return ( Queryable ) factory.getEntityPersister(className); } catch (MappingException me) { return null; } } ************************************************************* After the fix i ran few examples and some of my own programs which were previoulsy running/not-running both seem to work fine. I am surely interested to know about it. Please respond to the post or email to me at madhusudanmk@yahoo.com. Cheers, Madhusudan M Krishnamurthy Hibernate version:3.0 Mapping documents: <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping auto-import="false"> <import class="java.util.Map" rename="CatPersistentMap"/> <dynamic-class entity-name="CatPersistentMap" table="Cat" discriminator-value="C" persister="org.hibernate.persister.SingleTableEntityPersister"> <id name="id" type="string" unsaved-value="null" > <column name="CAT_ID" sql-type="char(32)" not-null="true"/> <generator class="uuid.hex"/> </id> <property name="name" type="java.lang.String"> <column name="NAME" length="16" not-null="true"/> </property> <property name="sex" type="char"/> <property name="weight" type="float" /> </dynamic-class> </hibernate-mapping> Code between sessionFactory.openSession() and session.close(): transaction = session.beginTransaction(); String hqlQuery = "from CatPersistentMap catPersistentMap"; Query query = session.createQuery(hqlQuery); System.out.println("SQL :: " + query.getQueryString()); List catList = query.list(); if (catList != null && !catList.isEmpty()) { Iterator catIterator = catList.iterator(); while (catIterator.hasNext()) { Map catMap = (Map) catIterator.next(); System.out.println("Cat Map :: " + catMap); } } else System.out.println("Empty records"); transaction.commit(); Full stack trace of any exception that occurs: java.sql.SQLException: ORA-00936: missing expression at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:96) at org.hibernate.loader.Loader.getResultSet(Loader.java:1044) at org.hibernate.loader.Loader.doQuery(Loader.java:321) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167) at org.hibernate.loader.Loader.doList(Loader.java:1201) at org.hibernate.loader.Loader.list(Loader.java:1186) at org.hibernate.hql.QueryTranslatorImpl.list(QueryTranslatorImpl.java:872) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:812) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:84) at com.accelrys.platform.test.TestCatQuery.executeCustomHQL(TestCatQuery.java:164) at com.accelrys.platform.test.TestCatQuery.main(TestCatQuery.java:53) SEVERE: Could not execute query java.sql.SQLException: ORA-00936: missing expression at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:96) at org.hibernate.loader.Loader.getResultSet(Loader.java:1044) at org.hibernate.loader.Loader.doQuery(Loader.java:321) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:167) at org.hibernate.loader.Loader.doList(Loader.java:1201) at org.hibernate.loader.Loader.list(Loader.java:1186) at org.hibernate.hql.QueryTranslatorImpl.list(QueryTranslatorImpl.java:872) at org.hibernate.impl.SessionImpl.find(SessionImpl.java:812) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:84) at com.accelrys.platform.test.TestCatQuery.executeCustomHQL(TestCatQuery.java:164) at com.accelrys.platform.test.TestCatQuery.main(TestCatQuery.java:53) Name and version of the database you are using: Oracle 9i The generated SQL (show_sql=true): select from

    Hibernate JIRA | 1 decade ago | Madhusudan M Krishnamurthy
    java.sql.SQLException: ORA-00936: missing expression

    26 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. java.sql.SQLException

      ORA-00932: inconsistent datatypes: expected NUMBER got DATE

      at oracle.jdbc.dbaccess.DBError.throwSqlException()
    2. oracle.jdbc.dbaccess
      DBError.throwSqlException
      1. oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      1 frame
    3. oracle.jdbc.ttc7
      TTC7Protocol.parseExecuteDescribe
      1. oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
      2. oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
      3. oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
      4. oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:831)
      4 frames
    4. Oracle jdbc
      OraclePreparedStatement.executeQuery
      1. oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2496)
      2. oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
      3. oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
      4. oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
      4 frames
    5. Hibernate
      CriteriaLoader.list
      1. org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:103)
      2. org.hibernate.loader.Loader.getResultSet(Loader.java:1065)
      3. org.hibernate.loader.Loader.doQuery(Loader.java:344)
      4. org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:190)
      5. org.hibernate.loader.Loader.doList(Loader.java:1271)
      6. org.hibernate.loader.Loader.list(Loader.java:1254)
      7. org.hibernate.loader.CriteriaLoader.list(CriteriaLoader.java:169)
      7 frames
    6. Hibernate
      CriteriaImpl.list
      1. org.hibernate.impl.SessionImpl.find(SessionImpl.java:2100)
      2. org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:274)
      2 frames
    7. com.entero.enterovision
      AgreementItemPriceSearchSidebar.find
      1. com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:340)
      2. com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:315)
      3. com.entero.enterovision.framework.service.AbstractService.fetch(AbstractService.java:281)
      4. com.entero.enterovision.service.agmt.AgmtService.fetchAgmtContractLines(AgmtService.java:488)
      5. com.entero.enterovision.components.agreementitemprice.AgreementController.find(AgreementController.java:71)
      6. com.entero.enterovision.components.AbstractController.findByParameters(AbstractController.java:32)
      7. com.entero.enterovision.components.ActiveWorkbench.populate(ActiveWorkbench.java:329)
      8. com.entero.enterovision.components.AbstractPMAWorkbench.populate(AbstractPMAWorkbench.java:30)
      9. com.entero.enterovision.components.AbstractSidebarSearchPanel.populateWorkbench(AbstractSidebarSearchPanel.java:241)
      10. com.entero.enterovision.components.agreementitemprice.AgreementItemPriceSearchSidebar.find(AgreementItemPriceSearchSidebar.java:203)
      10 frames
    8. com.entero.infrastructure
      SidebarPanel.executeFunction
      1. com.entero.infrastructure.ui.sidebar.SidebarPanel.executeFunction(SidebarPanel.java:158)
      1 frame
    9. com.entero.enterovision
      AbstractSidebarSearchPanel$2.actionPerformed
      1. com.entero.enterovision.components.AbstractSidebarSearchPanel.search(AbstractSidebarSearchPanel.java:183)
      2. com.entero.enterovision.components.AbstractSidebarSearchPanel.access$000(AbstractSidebarSearchPanel.java:32)
      3. com.entero.enterovision.components.AbstractSidebarSearchPanel$2.actionPerformed(AbstractSidebarSearchPanel.java:112)
      3 frames
    10. Java RT
      EventDispatchThread.run
      1. javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786)
      2. javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839)
      3. javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
      4. javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
      5. javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245)
      6. java.awt.Component.processMouseEvent(Component.java:5100)
      7. java.awt.Component.processEvent(Component.java:4897)
      8. java.awt.Container.processEvent(Container.java:1569)
      9. java.awt.Component.dispatchEventImpl(Component.java:3615)
      10. java.awt.Container.dispatchEventImpl(Container.java:1627)
      11. java.awt.Component.dispatchEvent(Component.java:3477)
      12. java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:3483)
      13. java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198)
      14. java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128)
      15. java.awt.Container.dispatchEventImpl(Container.java:1613)
      16. java.awt.Window.dispatchEventImpl(Window.java:1606)
      17. java.awt.Component.dispatchEvent(Component.java:3477)
      18. java.awt.EventQueue.dispatchEvent(EventQueue.java:456)
      19. java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
      20. java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
      21. java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145)
      22. java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
      23. java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
      23 frames