Hello,
I'm currently trying to map a NUMBER(10) field across to my domain object. I've tried mapping to a long, and to a BigDecimal, but I keep getting a sql error, invalid number (see below).
Basically the column holds an account type number, so no decimal places are required.
Versions: Oracle, 10.103, Hibernate 3.0
If anyone has achieved this and could supply me with any tips it would be most appreciated.

James Heffernan

Hibernate version:
Hibernate 3.0
Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
[DEBUG] HqlSqlBaseWalker - -query() >> end, level = 1
[DEBUG] AST - ---- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (lowusercustomers)
+-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
| +-[SELECT_EXPR] SelectExpressionImpl: 'lowusagecu0_.luid as luid' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=lucustomer,role=null,tableName=lowusercustomers,tableAlias=lowusagecu0_,colums={,className=nz.co.contactenergy.ivr.data.LowUsageCustomer}}}
| \-[SQL_TOKEN] SqlFragment: 'lowusagecu0_.ConsumerNumber as Consumer2_4_, lowusagecu0_.ReadFromDate as ReadFrom3_4_, lowusagecu0_.ReadToDate as ReadToDate4_, lowusagecu0_.NumberOfDays as NumberOf5_4_, lowusagecu0_.TotalUnits as TotalUnits4_, lowusagecu0_.AnnualConsumption as AnnualCo7_4_, lowusagecu0_.Confirmed as Confirmed4_, lowusagecu0_.Accessed as Accessed4_, lowusagecu0_.Extracted as Extracted4_, lowusagecu0_.Cancelled as Cancelled4_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[lucustomer], fromElementByTableAlias=[lowusagecu0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: 'lowusercustomers lowusagecu0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=lucustomer,role=null,tableName=lowusercustomers,tableAlias=lowusagecu0_,colums={,className=nz.co.contactenergy.ivr.data.LowUsageCustomer}}
\-[WHERE] SqlNode: 'where'
\-[EQ] SqlNode: '='
+-[DOT] DotNode: 'lowusagecu0_.ConsumerNumber' {propertyName=accountnumber,dereferenceType=4,propertyPath=accountnumber,path=lucustomer.accountnumber,tableAlias=lowusagecu0_,className=nz.co.contactenergy.ivr.data.LowUsageCustomer,classAlias=lucustomer}
| +-[ALIAS_REF] IdentNode: 'lowusagecu0_.luid' {alias=lucustomer, className=nz.co.contactenergy.ivr.data.LowUsageCustomer, tableAlias=lowusagecu0_}
| \-[IDENT] IdentNode: 'accountnumber' {originalText=accountnumber}
\-[PARAM] SqlNode: '?'

[DEBUG] ErrorCounter - -throwQueryException() : no errors
[DEBUG] QueryTranslatorImpl - -HQL: from nz.co.contactenergy.ivr.data.LowUsageCustomer as lucustomer where lucustomer.accountnumber = ?
[DEBUG] QueryTranslatorImpl - -SQL: select lowusagecu0_.luid as luid, lowusagecu0_.ConsumerNumber as Consumer2_4_, lowusagecu0_.ReadFromDate as ReadFrom3_4_, lowusagecu0_.ReadToDate as ReadToDate4_, lowusagecu0_.NumberOfDays as NumberOf5_4_, lowusagecu0_.TotalUnits as TotalUnits4_, lowusagecu0_.AnnualConsumption as AnnualCo7_4_, lowusagecu0_.Confirmed as Confirmed4_, lowusagecu0_.Accessed as Accessed4_, lowusagecu0_.Extracted as Extracted4_, lowusagecu0_.Cancelled as Cancelled4_ from lowusercustomers lowusagecu0_ where lowusagecu0_.ConsumerNumber=?
[DEBUG] ErrorCounter - -throwQueryException() : no errors
[DEBUG] AbstractBatcher - -about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[DEBUG] ConnectionManager - -opening JDBC connection
[DEBUG] SQL - -select lowusagecu0_.luid as luid, lowusagecu0_.ConsumerNumber as Consumer2_4_, lowusagecu0_.ReadFromDate as ReadFrom3_4_, lowusagecu0_.ReadToDate as ReadToDate4_, lowusagecu0_.NumberOfDays as NumberOf5_4_, lowusagecu0_.TotalUnits as TotalUnits4_, lowusagecu0_.AnnualConsumption as AnnualCo7_4_, lowusagecu0_.Confirmed as Confirmed4_, lowusagecu0_.Accessed as Accessed4_, lowusagecu0_.Extracted as Extracted4_, lowusagecu0_.Cancelled as Cancelled4_ from lowusercustomers lowusagecu0_ where lowusagecu0_.ConsumerNumber=?
[DEBUG] AbstractBatcher - -about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[DEBUG] JDBCExceptionReporter - -could not execute query [select lowusagecu0_.luid as luid, lowusagecu0_.ConsumerNumber as Consumer2_4_, lowusagecu0_.ReadFromDate as ReadFrom3_4_, lowusagecu0_.ReadToDate as ReadToDate4_, lowusagecu0_.NumberOfDays as NumberOf5_4_, lowusagecu0_.TotalUnits as TotalUnits4_, lowusagecu0_.AnnualConsumption as AnnualCo7_4_, lowusagecu0_.Confirmed as Confirmed4_, lowusagecu0_.Accessed as Accessed4_, lowusagecu0_.Extracted as Extracted4_, lowusagecu0_.Cancelled as Cancelled4_ from lowusercustomers lowusagecu0_ where lowusagecu0_.ConsumerNumber=?] <java.sql.SQLException: ORA-01722: invalid number
>java.sql.SQLException: ORA-01722: invalid number

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:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:751)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:312)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:742)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:738)
at nz.co.contactenergy.ivr.data.oracle.HibernateLowUsageCustomerDao.getByAccountnumber(Unknown Source)
at nz.co.contactenergy.ivr.data.oracle.HibernateOracleDataChannel.getCustomer(Unknown Source)
at nz.co.contactenergy.ivr.data.oracle.HibernateOracleDataChannel.buildDataSet(Unknown Source)
at nz.co.contactenergy.ivr.data.oracle.HibernateOracleDataChannel.buildLowUserCustomer(Unknown Source)
at nz.co.contactenergy.ivr.data.oracle.HibernateOracleDataChannel.invokeTransaction(Unknown Source)
at nz.co.contactenergy.ivr.data.DefaultDataBuilder.getDCData(Unknown Source)
at nz.co.contactenergy.ivr.data.DefaultDataBuilder.getData(Unknown Source)
at nz.co.contactenergy.ivr.DefaultModelGenerator.buildData(Unknown Source)
at nz.co.contactenergy.ivr.DefaultModelGenerator.getModel(Unknown Source)
at nz.co.contactenergy.ivr.ControllerServlet.setupModel(Unknown Source)
at nz.co.contactenergy.ivr.ControllerServlet.controller(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:351)
at org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:305)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:128)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:44)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:684)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:625)
at org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:386)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:346)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:700)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:584)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)

[WARN] JDBCExceptionReporter - -SQL Error: 1722, SQLState: 42000
[ERROR] JDBCExceptionReporter - -ORA-01722: invalid number

[DEBUG] SQLErrorCodeSQLExceptionTranslator - -Translating SQLException with SQLState '42000' and errorCode '1722' and message [ORA-01722: invalid number
]; SQL was [select lowusagecu0_.luid as luid, lowusagecu0_.ConsumerNumber as Consumer2_4_, lowusagecu0_.ReadFromDate as ReadFrom3_4_, lowusagecu0_.ReadToDate as ReadToDate4_, lowusagecu0_.NumberOfDays as NumberOf5_4_, lowusagecu0_.TotalUnits as TotalUnits4_, lowusagecu0_.AnnualConsumption as AnnualCo7_4_, lowusagecu0_.Confirmed as Confirmed4_, lowusagecu0_.Accessed as Accessed4_, lowusagecu0_.Extracted as Extracted4_, lowusagecu0_.Cancelled as Cancelled4_ from lowusercustomers lowusagecu0_ where lowusagecu0_.ConsumerNumber=?] for task [Hibernate operation: could not execute query]

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

You didn't show the code you use to execute the query. Specifically the parameter binding.

Hi:

I have a similar error with binding a Number from an Oracle DB to an Integer in my object Here is the DB definition and PO. Will look at it more tomorrow some more and let you know if I get an answer. I thought it might have been what I put in that column for data but after updating that column I get the same message.

PObject
public class SkillsetAdmin extends BasePO {
private SkillsetAdminKey id;
private String skillsetName;
private String skillsetType;
private Integer configID;
private Set skillsetSiteAssociations;
etc...


DB Description
SQL> describe skillsetadmin;
Name Null? Type
----------------------------------------- -------- ----------------------------
CONFIGID NOT NULL NUMBER
SKILLSETNAME NOT NULL VARCHAR2(40)

Hibernate Debug
##PEM done materializing entity [com.t.ccs.adminRouting.model.po.SkillsetAdmin#com.telus.ccs.adminRouting.model.po.SkillsetAdminKey@45466b[skillsetName=ccw_ab_silver_ctx,configID=1]]
##PEM initializing non-lazy collections
##PEM initializing collection [com.t.ccs.adminRouting.model.po.SkillsetAdmin.skillsetSiteAssociations#com.t.ccs.adminRouting.model.po.SkillsetAdminKey@45466b[skillsetName=ccw_ab_silver_ctx,configID=1]]
##PEM checking second-level cache
##PEM collection not cached
##PEM about to open: 0 open PreparedStatements, 0 open ResultSets
##PEM select skillsetsi0_.configID as configID__, skillsetsi0_.skillsetName as skillset2___, skillsetsi0_.id as id__, skillsetsi0_.id as id0_, skillsetsi0_.skillsetName as skillset2_0_, skillsetsi0_.siteName as siteName0_, skillsetsi0_.percentage as percentage0_, skillsetsi0_.phoneNumber as phoneNum5_0_, skillsetsi0_.configID as configID0_ from SkillsetSiteAssociationAdmin skillsetsi0_ where skillsetsi0_.configID=? and skillsetsi0_.skillsetName=?
Hibernate: select skillsetsi0_.configID as configID__, skillsetsi0_.skillsetName as skillset2___, skillsetsi0_.id as id__, skillsetsi0_.id as id0_, skillsetsi0_.skillsetName as skillset2_0_, skillsetsi0_.siteName as siteName0_, skillsetsi0_.percentage as percentage0_, skillsetsi0_.phoneNumber as phoneNum5_0_, skillsetsi0_.configID as configID0_ from SkillsetSiteAssociationAdmin skillsetsi0_ where skillsetsi0_.configID=? and skillsetsi0_.skillsetName=?
##PEM preparing statement
##PEM binding 'ccw_ab_silver_ctx' to parameter: 1
##PEM binding '1' to parameter: 2[Loaded oracle.net.ns.Message]
[Loaded oracle.net.ns.Message11]
[Loaded net.sf.hibernate.util.JDBCExceptionReporter]
[Loaded org.apache.log4j.spi.ThrowableInformation]
[Loaded org.apache.log4j.spi.VectorWriter]
[Loaded org.apache.log4j.spi.NullWriter]
##PEM SQL Exception
java.sql.SQLException: ORA-01722: invalid number

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:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1253)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2532)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2850)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:609)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:537)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:990)
at net.sf.hibernate.loader.Loader.loadCollection(Loader.java:965)
at net.sf.hibernate.loader.OneToManyLoader.initialize(OneToManyLoader.java:93)
at net.sf.hibernate.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:284)
at net.sf.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:3268)
at net.sf.hibernate.collection.PersistentCollection.forceInitialization(PersistentCollection.java:336)
at net.sf.hibernate.impl.SessionImpl.initializeNonLazyCollections(SessionImpl.java:3123)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1521)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1513)
at com.t.ccs.util.DAO.read(DAO.java:100)
at test.SkillsetDAOtest.main(SkillsetDAOtest.java:36)
##PEM SQL Error: 1722, SQLState: 42000
##PEM ORA-01722: invalid number
##PEM done closing: 0 open PreparedStatements, 0 open ResultSets
##PEM closing statement
[Loaded net.sf.hibernate.exception.NestableDelegate]
##PEM SQL Exception
java.sql.SQLException: ORA-01722: invalid number

what does the mapping xml look like?

And what length is the NUMBER ?
For NUMBER(x) you can better use Long.
For NUMBER(x,y) you can use BigDecimal.

Integer is used for INT, etc.

Thanks for the feedback on Oracle types and the considerations for binding of variables. Oracle type integer actually is equivalent to Number(38). Here is some more detail.

DB Description:

SQL> describe skillsetadmin;
Name Null? Type
----------------------------------------- -------- ----------------------------
CONFIGID NOT NULL NUMBER(38)
SKILLSETNAME NOT NULL VARCHAR2(40)


Hibernate mapping snippet:

<hibernate-mapping>
<class
name="com.telus.ccs.adminRouting.model.po.SkillsetAdmin"
table="SkillsetAdmin"
dynamic-update="false"
dynamic-insert="false"
>

<composite-id
name="id"
class="com.telus.ccs.adminRouting.model.po.SkillsetAdminKey"
>
<key-property
name="skillsetName"
type="java.lang.String"
column="skillsetName"
length="40"
/>

/>
<key-property
name="configID"
type="java.lang.Integer"
column="configID"

</composite-id>
<property
name="skillsetType"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="skillsetType"
length="20"
not-null="true"
/>

<set
name="skillsetSiteAssociations"
lazy="false"
inverse="false"
cascade="all"
sort="unsorted"
>

<key>
<column name="configID" not-null="true"/>
<column name="skillsetName" not-null="true"/>
</key>

<one-to-many
class="com.t.ccs.adminRouting.model.po.SkillsetSiteAssociationAdmin"
/>
</set>


</class>

</hibernate-mapping>

Hi:

My problem was a result of a mapping error in the key (wrong order) but the error made me believe that is was a type mismatch between Oracle and Java. Perhaps you have the same problem and should check your mappings once more. I can map Number(10) to Integer without a problem which is all you should need if there is no decimal places.

Alex.