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 "Givler, Eric" <eg...@state.pa.us> on 2008/09/11 03:05:07 UTC
IBatis Random ORA-01722: invalid number error
We have a production application that is periodically returning an error from a query. In fact, the user refreshes the page and the same query runs fine. The query takes a hashmap as a parameter and returns an ArrayList of hashmaps. When we examine the logs, in which we can see the query and the jdbc parameters and resultsets, the parameters and query are identical between calls, except the first call fails.
The mapping query is like this:
<select id="getDatabaseRows" paramClass="map" resultClass="java.util.HashMap">
select c.descrip
, sum(v.item_count) as count
from table1 v
, table2 c
where v.id = #id#
and v.codeid=c.codeid
group by c.descrip
</select>
The call is like:
HashMap params = new HashMap();
params.add( "id", new Long("123");
List result = (ArrayList) sqlMap.queryForList("Namespace.getDatabaseRows", params );
If we cut&paste the query that is failing into other tools, like SQL*Plus, or TOAD for Oracle, it runs fine. In fact, if we step it, it runs fine. Today, out of the 70 times it executed, it failed 4 times. The user refreshed the page each time and the query ran without error.
Is there anything we can do to track down what is happening here? It seems like the parameter must be somehow read/treated as a character on the first call which would make it fail, but we see in the log that it's a java.lang.Long. If it helps, we are in a Java 1.4.2 environment under ORacle Application Server 10.1.2.1, and the iBATIS version is 2.1.7.597. The backend database is Oracle 10G Release 2 10.2.0.3 I think.
Thanks for any help,
Eric
Re: IBatis Random ORA-01722: invalid number error
Posted by Giovanni Cuccu <gi...@gmail.com>.
a logon trigger is ok, if you like you could transform them in two
alter system statements.
Once you got the oracle trace file with the errors we can get in touch
to see what is happening.
Giovanni
On Thu, Sep 11, 2008 at 2:05 PM, Givler, Eric <eg...@state.pa.us> wrote:
> For the first two alter session statements, would those occur within a logon trigger for the account? I'm wondering what you mean by "after you get the connection". For the other two, I can just write two additional statements that are fired before and after this query.
>
> I really appreciate the assistance on this. It's happening sporadically in 3 different queries. They all use HashMaps as parameters and HashMaps as the return results. I don't know if that's an issue, but I thought I'd mention it again.
>
> -----Original Message-----
> From: Giovanni Cuccu [mailto:giovanni.cuccu@gmail.com]
> Sent: Thursday, September 11, 2008 2:55 AM
> To: user-java@ibatis.apache.org
> Subject: Re: IBatis Random ORA-01722: invalid number error
>
>
> Eric,
> if you have the iBatis logs and all seems ok I suggest you to enable the oracle trace with the following instructions these one should be issued after you get the connection alter session set timed_statistics=true; alter session set max_dump_file_size=unlimited;
>
> these one should be issued before executing the sql
> alter session set events '10046 trace name context forever, level 12' ;
>
> these one should be issued after executing the sql
> alter session set events '10046 trace name context off';
>
> Oracle should produce a trc file in the $ORACLE_HOME/<some_path>/udump directory of the server you can analyze the file by yourself (it's not veary understandable at firt sigth) or with tkprof. in the trc files you can see the value of the bind variable, the type and the error. with these information it should be easy to diagnose the issue. Giovanni
>
>
> On Thu, Sep 11, 2008 at 3:05 AM, Givler, Eric <eg...@state.pa.us> wrote:
>> We have a production application that is periodically returning an
>> error from a query. In fact, the user refreshes the page and the same
>> query runs fine. The query takes a hashmap as a parameter and returns
>> an ArrayList of hashmaps. When we examine the logs, in which we can
>> see the query and the jdbc parameters and resultsets, the parameters
>> and query are identical between calls, except the first call fails.
>>
>> The mapping query is like this:
>> <select id="getDatabaseRows" paramClass="map" resultClass="java.util.HashMap">
>> select c.descrip
>> , sum(v.item_count) as count
>> from table1 v
>> , table2 c
>> where v.id = #id#
>> and v.codeid=c.codeid
>> group by c.descrip
>> </select>
>>
>>
>> The call is like:
>> HashMap params = new HashMap();
>> params.add( "id", new Long("123");
>> List result = (ArrayList)
>> sqlMap.queryForList("Namespace.getDatabaseRows", params );
>>
>> If we cut&paste the query that is failing into other tools, like
>> SQL*Plus, or TOAD for Oracle, it runs fine. In fact, if we step it,
>> it runs fine. Today, out of the 70 times it executed, it failed 4
>> times. The user refreshed the page each time and the query ran
>> without error.
>>
>> Is there anything we can do to track down what is happening here? It
>> seems like the parameter must be somehow read/treated as a character
>> on the first call which would make it fail, but we see in the log that
>> it's a java.lang.Long. If it helps, we are in a Java 1.4.2
>> environment under ORacle Application Server 10.1.2.1, and the iBATIS
>> version is 2.1.7.597. The backend database is Oracle 10G Release 2
>> 10.2.0.3 I think.
>>
>> Thanks for any help,
>> Eric
>>
>
>
>
> --
> --------------------------------------------------------------------
> "You don't know the power of dark side" - Darth Vader
>
--
--------------------------------------------------------------------
"You don't know the power of dark side" - Darth Vader
RE: IBatis Random ORA-01722: invalid number error
Posted by "Givler, Eric" <eg...@state.pa.us>.
For the first two alter session statements, would those occur within a logon trigger for the account? I'm wondering what you mean by "after you get the connection". For the other two, I can just write two additional statements that are fired before and after this query.
I really appreciate the assistance on this. It's happening sporadically in 3 different queries. They all use HashMaps as parameters and HashMaps as the return results. I don't know if that's an issue, but I thought I'd mention it again.
-----Original Message-----
From: Giovanni Cuccu [mailto:giovanni.cuccu@gmail.com]
Sent: Thursday, September 11, 2008 2:55 AM
To: user-java@ibatis.apache.org
Subject: Re: IBatis Random ORA-01722: invalid number error
Eric,
if you have the iBatis logs and all seems ok I suggest you to enable the oracle trace with the following instructions these one should be issued after you get the connection alter session set timed_statistics=true; alter session set max_dump_file_size=unlimited;
these one should be issued before executing the sql
alter session set events '10046 trace name context forever, level 12' ;
these one should be issued after executing the sql
alter session set events '10046 trace name context off';
Oracle should produce a trc file in the $ORACLE_HOME/<some_path>/udump directory of the server you can analyze the file by yourself (it's not veary understandable at firt sigth) or with tkprof. in the trc files you can see the value of the bind variable, the type and the error. with these information it should be easy to diagnose the issue. Giovanni
On Thu, Sep 11, 2008 at 3:05 AM, Givler, Eric <eg...@state.pa.us> wrote:
> We have a production application that is periodically returning an
> error from a query. In fact, the user refreshes the page and the same
> query runs fine. The query takes a hashmap as a parameter and returns
> an ArrayList of hashmaps. When we examine the logs, in which we can
> see the query and the jdbc parameters and resultsets, the parameters
> and query are identical between calls, except the first call fails.
>
> The mapping query is like this:
> <select id="getDatabaseRows" paramClass="map" resultClass="java.util.HashMap">
> select c.descrip
> , sum(v.item_count) as count
> from table1 v
> , table2 c
> where v.id = #id#
> and v.codeid=c.codeid
> group by c.descrip
> </select>
>
>
> The call is like:
> HashMap params = new HashMap();
> params.add( "id", new Long("123");
> List result = (ArrayList)
> sqlMap.queryForList("Namespace.getDatabaseRows", params );
>
> If we cut&paste the query that is failing into other tools, like
> SQL*Plus, or TOAD for Oracle, it runs fine. In fact, if we step it,
> it runs fine. Today, out of the 70 times it executed, it failed 4
> times. The user refreshed the page each time and the query ran
> without error.
>
> Is there anything we can do to track down what is happening here? It
> seems like the parameter must be somehow read/treated as a character
> on the first call which would make it fail, but we see in the log that
> it's a java.lang.Long. If it helps, we are in a Java 1.4.2
> environment under ORacle Application Server 10.1.2.1, and the iBATIS
> version is 2.1.7.597. The backend database is Oracle 10G Release 2
> 10.2.0.3 I think.
>
> Thanks for any help,
> Eric
>
--
--------------------------------------------------------------------
"You don't know the power of dark side" - Darth Vader
Re: IBatis Random ORA-01722: invalid number error
Posted by Giovanni Cuccu <gi...@gmail.com>.
Eric,
if you have the iBatis logs and all seems ok I suggest you to
enable the oracle trace with the following instructions
these one should be issued after you get the connection
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
these one should be issued before executing the sql
alter session set events '10046 trace name context forever, level 12' ;
these one should be issued after executing the sql
alter session set events '10046 trace name context off';
Oracle should produce a trc file in the $ORACLE_HOME/<some_path>/udump
directory of the server
you can analyze the file by yourself (it's not veary understandable at
firt sigth) or with tkprof.
in the trc files you can see the value of the bind variable, the type
and the error. with these information it should be easy to diagnose
the issue.
Giovanni
On Thu, Sep 11, 2008 at 3:05 AM, Givler, Eric <eg...@state.pa.us> wrote:
> We have a production application that is periodically returning an error from a query. In fact, the user refreshes the page and the same query runs fine. The query takes a hashmap as a parameter and returns an ArrayList of hashmaps. When we examine the logs, in which we can see the query and the jdbc parameters and resultsets, the parameters and query are identical between calls, except the first call fails.
>
> The mapping query is like this:
> <select id="getDatabaseRows" paramClass="map" resultClass="java.util.HashMap">
> select c.descrip
> , sum(v.item_count) as count
> from table1 v
> , table2 c
> where v.id = #id#
> and v.codeid=c.codeid
> group by c.descrip
> </select>
>
>
> The call is like:
> HashMap params = new HashMap();
> params.add( "id", new Long("123");
> List result = (ArrayList) sqlMap.queryForList("Namespace.getDatabaseRows", params );
>
> If we cut&paste the query that is failing into other tools, like SQL*Plus, or TOAD for Oracle, it runs fine. In fact, if we step it, it runs fine. Today, out of the 70 times it executed, it failed 4 times. The user refreshed the page each time and the query ran without error.
>
> Is there anything we can do to track down what is happening here? It seems like the parameter must be somehow read/treated as a character on the first call which would make it fail, but we see in the log that it's a java.lang.Long. If it helps, we are in a Java 1.4.2 environment under ORacle Application Server 10.1.2.1, and the iBATIS version is 2.1.7.597. The backend database is Oracle 10G Release 2 10.2.0.3 I think.
>
> Thanks for any help,
> Eric
>
--
--------------------------------------------------------------------
"You don't know the power of dark side" - Darth Vader