ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joakim Olsson <argo...@lingonpaj.com>
Subject Re: java.sql.SQLException: ORA-01722: invalid number
Date Sun, 24 Aug 2008 07:57:40 GMT
When you subtract two dates the result already is a number.

Using to_number on the result will make Oracle do an implicit conversion
to CHAR and then trying to convert it back again. Depending on your
NLS-parameters that implicit conversion will break stuff.

Regards,
Joakim


On Sat, 2008-08-23 at 19:36 -0700, kiran vuppla wrote:
>   
> 
> I am getting the following error when I try to convert the diff of
> dates to to_number(). Can some one help me will be appreciated. I ran
> the same thing from ORACLE TOAD and it is giving the expected
> results. 
> 
>   
> 
> The SQL I am trying to convert the diff of two dates to number:
> 
> floor(to_number(#endDate# - least(#startDate# + ((nDays+1) *
> #granularity#), #endDate#)))
>  
> ERROR:
>   
> 
> --- Cause: java.sql.SQLException: ORA-01722: invalid number
> 
> at
> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)

> 
> at
> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)

> 
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)

> 
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)

> 
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)

> 
> at org.springframework.orm.ibatis.SqlMapClientTemplate
> $3.doInSqlMapClient(SqlMapClientTemplate.java:298) 
> 
> at
> org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)

> 
> ... 72 more 
> 
> Caused by: java.sql.SQLException: ORA-01722: invalid number
> 
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145) 
> 
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) 
> 
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) 
> 
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) 
> 
> at
> oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216) 
> 
> at
> oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1120)

> 
> at
> oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1144) 
> 
> at
> oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:962)

> 
> at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1243) 
> 
> at
> oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)

> 
> at
> oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3520)

> 
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)

> 
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
> 
> at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) 
> 
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
> 
> at java.lang.reflect.Method.invoke(Unknown Source) 
> 
> at
> com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)

> 
> at $Proxy56.execute(Unknown Source) 
> 
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185) 
> 
> at
> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)

> 
> at
> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)

> 
> ... 78 more 
> 
>   
> 
> Thanks
> Kiran
> 
> 
> 


Mime
View raw message