ORA-01003 no statement parsed
Deyan D. Bektchiev
We are getting the exception below when executing a simple prepared
statement.
Does anyone have idea why it could happen?
This is the code snippet where it occurs:
PreparedStatement ps = null;
PreparedStatement ps2 = null;
try
{
String statement = "select " + DATA + " from " + TABLE +
" where ROWNUM = 1 and " + SENT +
"=0 AND (SysDate - " + INS_DATE +
") >= ? " +
" for update order by " +
SEQ_NUMBER + " asc";
ps = con.prepareStatement(statement);
ps.setDouble(1, minAge);
ResultSet rs = ps.executeQuery();
if(rs.next())
{
Event evt = readData(rs);
long seq = evt.seqNumber;
long newSeq = newSentSeqNumber(con, evt);
evt.seqNumber = newSeq;
ps2 = con.prepareStatement("update " + TABLE + " set " +
SEQ_NUMBER + "= ?, " +
SENT + "= 1 where " +
SEQ_NUMBER +
"=? and " + SENT + "=0");
ps2.setLong(1, newSeq);
ps2.setLong(2, seq);
if(ps2.executeUpdate()==1)
{
return evt;
}
}
}
catch(Exception ex)
{
JRiskLocale.exception(ex);
return null;
}
finally
{
closeStatement(ps);
closeStatement(ps2);
}
Thanks,
Dejan
java.sql.SQLException: ORA-01003: no statement parsed
at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:542)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1330)
at
oracle.jdbc.ttc7.TTC7Protocol.executeFetch(TTC7Protocol.java:680)
at
oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1695)
at
oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1758)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java
:1807)
at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedState
ment.java:332)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatem
ent.java:283)
at
weblogic.jdbc.pool.PreparedStatement.executeQuery(PreparedStatement.java:51)
at
weblogic.jdbc.rmi.internal.PreparedStatementImpl.executeQuery(PreparedStatem
entImpl.java:56)
at
weblogic.jdbc.rmi.SerialPreparedStatement.executeQuery(SerialPreparedStateme
nt.java:42)
Joseph Weinstein
Joe
Deyan D. Bektchiev
problem in the morning after leaving the server running overnight.
This prepared statement is executed at least once every thirty seconds (started by
a timer in a startup class).
--dejan
Joseph Weinstein
"Deyan D. Bektchiev" wrote:
> It works for quite some time even inside Weblogic but our guys in the UK saw the
> problem in the morning after leaving the server running overnight.
> This prepared statement is executed at least once every thirty seconds (started by
> a timer in a startup class).
>
> --dejan
Interesting... How often is the connection obtained/closed? Is the connection obtained
every time the rest of this code runs, or is it a conenction that is obtained and held
indefinitely?
Deyan D. Bektchiev
time after the execution completes (if nothing is fond in the DB which is 99% of the time
the whole thing should complete in less than 0.5s).
I noticed that I do not close the ResultSet inside the code though.
Could it have something to do with the error?
--dejan
Joseph Weinstein
"Deyan D. Bektchiev" wrote:
> The connection is obtained just before calling the prepared statement and closed every
> time after the execution completes (if nothing is fond in the DB which is 99% of the time
> the whole thing should complete in less than 0.5s).
> I noticed that I do not close the ResultSet inside the code though.
>
> Could it have something to do with the error?
Not likely. By JDBC spec, a closing connection should automatically close any remaining
statements, and any statement closing should automatically close any remaining resultset...
Do you have testConnsOnReserve true? Actually, it doesn't matter in this case, because
the connection is clearly OK because it's returning a DBMS failure message.
How many connections are in the connection pool? The error message seems to indicate
that the DBMS has lost the query plan that it was re-using with this prepared statement.
What version of weblogic are you running? Did anything happen in the DBMS while or
before this happened? Eg: Did the table you're querying get changed in any way?
Joe
Deyan D. Bektchiev
pool configuration:
<JDBCConnectionPool CapacityIncrement="1"
DriverName="oracle.jdbc.driver.OracleDriver" InitialCapacity="3"
LoginDelaySeconds="1" MaxCapacity="10" Name="defaultPool"
Properties="user=test1;password=test1;dll=ocijdbc8;protocol=thin;prefetch=10"
RefreshMinutes="1" ShrinkPeriodMinutes="15"
ShrinkingEnabled="false" Targets="myserver"
TestTableName="TestJdbcConnection" URL="jdbc:oracle:thin:@bench1:1521:orcl"/>
The weblogic version is 6.1SP2 and it's running on NT4.0 .
The table definition itself couldn't have changed in the mean time but a few rows could have
been added in the mean time.
And the strange thing is that they say it was happening continuously and not just once.
Joseph Weinstein
"Deyan D. Bektchiev" wrote:
> We don't have testConnsOnReserve set since it's just on a development machine and here is the
> pool configuration:
>
> <JDBCConnectionPool CapacityIncrement="1"
> DriverName="oracle.jdbc.driver.OracleDriver" InitialCapacity="3"
> LoginDelaySeconds="1" MaxCapacity="10" Name="defaultPool"
> Properties="user=test1;password=test1;dll=ocijdbc8;protocol=thin;prefetch=10"
> RefreshMinutes="1" ShrinkPeriodMinutes="15"
> ShrinkingEnabled="false" Targets="myserver"
> TestTableName="TestJdbcConnection" URL="jdbc:oracle:thin:@bench1:1521:orcl"/>
>
> The weblogic version is 6.1SP2 and it's running on NT4.0 .
> The table definition itself couldn't have changed in the mean time but a few rows could have
> been added in the mean time.
> And the strange thing is that they say it was happening continuously and not just once.
That's not strange. We cache prepared statements now in the pool, so once they go bad,
they'll stay bad. Usually it's because something happened in the DBMS to invalidate the
query plan associated with the statement, and usually this is some DDL change. Rows
added can't cause this. At this point I believe it's a DBMS internal problem. One workaround
in 6.1 is to turn off statement caching in the pool. Then every time your code prepares a
statement for the one use, that's what will happen in the pool too.
Deyan D. Bektchiev
and I thought it was already fixed in 6.1. Is it a new issue (then I'll file a case with support)
or is it a known issue and if it is the latter is there a patch available for it?
Thanks,
Joseph Weinstein
> I thought it could be it but I've seem patches for refreshing prepared statements for 6.0 and 5.1
> and I thought it was already fixed in 6.1. Is it a new issue (then I'll file a case with support)
> or is it a known issue and if it is the latter is there a patch available for it?
The problem you describe so far, has not been seen before. I believe the problem/issue is
something happening inside the Oracle DBMS, not in any JDBC driver, nor in Weblogic.
What I am suggesting is that you use an existing feature (actually turn off an optimization)
of the weblogic server to work around the Oracle problem.
I believe that if you set the pool's statement cache size to zero the Oracle problem will
be prevented, at the cost of somewhat slower performance of your PreparedStatements.
One more thing to try, just to cover the bases, is to download and use Oracle's latest
version of their thin driver instead of the older one we ship. There is a *small* possibility
that there is a driver issue they may have fixed...
Joe
Deyan D. Bektchiev
Unfortunately the driver we use is already the latest available for 8.1.6 and I guess we are out of
luck on this one.
Thanks a lot for your help!
Joseph Weinstein
> Thanks Joe,
> Unfortunately the driver we use is already the latest available for 8.1.6 and I guess we are out of
> luck on this one.
> Thanks a lot for your help!
Well, I would talk to your DBA and see if there's any configuration issue regarding the amount
of memory allotted to a given connection for storing query plans, and whether it is alterable,
and whether this cache can be invalidated by the DBMS. To repeat, I am fairly confident that
if you turn off statement caching, the problem should go away.