You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by kiran vuppla <ki...@yahoo.com> on 2008/08/24 04:36:15 UTC
java.sql.SQLException: ORA-01722: invalid number
�
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
Re: java.sql.SQLException: ORA-01722: invalid number
Posted by kiran vuppla <ki...@yahoo.com>.
Thanks for the reply.
�
Actually I did without having the to_number() but still it is giving me the issue. I am trying to do the following inside select statement:
�
select
(case
when #endDate# - least(#startDate# + (nDays * #granularity#), #endDate#) < #granularity#
then #endDate# - (#granularity# - 1)
else least(#startDate# + ((nDays) * #granularity#), #endDate#)
end )
�
when I use to_number() in the difference it says:
Got interval of time expected NUMBER
�
when I dont use to_number() in the diff of two dates:
I get Expected Interval of time got NUMBER.
ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got NUMBER
�
I would appreciate if some one can help in solving the issue.
�
Thanks,
Kiran
--- On Sun, 8/24/08, Joakim Olsson <ar...@lingonpaj.com> wrote:
From: Joakim Olsson <ar...@lingonpaj.com>
Subject: Re: java.sql.SQLException: ORA-01722: invalid number
To: "iBatis Forum" <us...@ibatis.apache.org>
Date: Sunday, August 24, 2008, 2:57 AM
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
>
>
>
Re: java.sql.SQLException: ORA-01722: invalid number
Posted by kiran vuppla <ki...@yahoo.com>.
Thanks for the reply.
�
Actually I did without having the to_number() but still it is giving me the issue. I am trying to do the following inside select statement:
�
select
(case
when #endDate# - least(#startDate# + (nDays * #granularity#), #endDate#) < #granularity#
then #endDate# - (#granularity# - 1)
else least(#startDate# + ((nDays) * #granularity#), #endDate#)
end )
�
when I use to_number() in the difference it says:
Got interval of time expected NUMBER
�
when I dont use to_number() in the diff of two dates:
I get Expected Interval of time got NUMBER.
ORA-00932: inconsistent datatypes: expected INTERVAL DAY TO SECOND got NUMBER
�
I would appreciate if some one can help in solving the issue.
�
Thanks,
Kiran
--- On Sun, 8/24/08, Joakim Olsson <ar...@lingonpaj.com> wrote:
From: Joakim Olsson <ar...@lingonpaj.com>
Subject: Re: java.sql.SQLException: ORA-01722: invalid number
To: "iBatis Forum" <us...@ibatis.apache.org>
Date: Sunday, August 24, 2008, 2:57 AM
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
>
>
>
Re: java.sql.SQLException: ORA-01722: invalid number
Posted by Joakim Olsson <ar...@lingonpaj.com>.
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
>
>
>