Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

ORA-01002: fetch out of sequence on getObject with Oracle, under load

38 views
Skip to first unread message

Nicolas Strauel

unread,
Dec 15, 2004, 4:55:54 AM12/15/04
to
As we start response time testing, we encounter the following issue when connecting approximately 6 users simultaneously, doing the same query with no think time :

java.sql.SQLException: ORA-01002: fetch out of sequence

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2355)
at oracle.jdbc.oci8.OCIDBAccess.fetch(OCIDBAccess.java:2028)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:534)
at oracle.jdbc.driver.OracleStatement.getCursorValue(OracleStatement.java:3899)
at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:6104)
at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5892)
at oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:707)
at weblogic.jdbc.pool.Statement.getObject(Statement.java:745)
at weblogic.jdbc.rmi.internal.CallableStatementImpl.getObject(CallableStatementImpl.java:184)
at weblogic.jdbc.rmi.SerialCallableStatement.getObject(SerialCallableStatement.java:291)
at com.cs.tnp.tools.access.db.FLKCallableStatementWrapper.getObject(Unknown Source)
at com.cs.tnp.datamodel.op.order.OrderFactory.searchByPredefinedCriteria(Unknown Source)
at com.cs.tnp.datamodel.op.order.OrderBehaviour.searchOrdersByPredefinedCriteria(Unknown Source)
at com.cs.tnp.service.impl.op.ServicePredefinedOrderSearch.executeImpl(Unknown Source)
at com.cs.tnp.service.impl.AbstractDefaultService.execute(Unknown Source)
at com.cs.tnp.service.engine.jms.MDBServiceDispatcherDelegate.onMessage(Unknown Source)
at com.cs.tnp.op.ejb.ServiceOPReadEJB.onMessage(Unknown Source)
at com.cs.tnp.op.ejb.ServiceOPReadEJB_5ds24x_EOImpl.onMessage(ServiceOPReadEJB_5ds24x_EOImpl.java:37)
at com.cs.tnp.service.engine.demultiplexer.ServiceActivatorListener.onMessage(Unknown Source)
at weblogic.jms.client.JMSSession.onMessage(JMSSession.java:1865)
at weblogic.jms.client.JMSSession.execute(JMSSession.java:1819)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)

This is happening when doing a getObject on a CallableStatement (a PL/SQL stored procedure call), which is normally returning a cursor opened inside the PL/SQL code.
This problem does not appear with less users (for example : 3 works fine).

Here is the corresponding code :

public List searchByPredefinedCriteria(
ParticipantRoleId pParticipantRoleId,
int pScreenMenu,
int pEventConsoleState,
String pOrderBy,
int pStartRow,
int pPageSize,
ReplyType pReply,
List pErrorCodes,
Session pSession)
throws SQLException {
List searchResults = null;

Connection connection = null;
CallableStatement cstmt = null;
ResultSet rs = null;
int itemNb = 0;
try {
// checking parameters aren't null
if (pParticipantRoleId != null) {

connection = getConnection();

// setting parameters on Callable statement
cstmt = connection.prepareCall(PS_PRED_ORDER_SEARCH_ORDER);
setAndRegisterParameters(
pParticipantRoleId,
pScreenMenu,
pEventConsoleState,
pOrderBy,
false,
pStartRow,
pPageSize,
cstmt);

boolean isCancellationRequest =
isPredefinedOrderSearchCancellationScreen(
pScreenMenu,
pEventConsoleState);

cstmt.execute();
rs = (ResultSet) cstmt.getObject(7);

if (!rs.next()) {
// generate ERROR code, there is no data corresponding to this id
pErrorCodes.add(
OrderErrorCodes
.createErrorNoOrdersFoundForSearchCriteria(
pSession));

} else {

searchResults = new ArrayList();
Order order = null;
//multipage management
// if ((itemNb >= minLine) && (itemNb < maxLine)) { //in download mode all the results are return not omly the page asked

do {

order =
readFromOrderAndCancellationSummaryRS(
pParticipantRoleId.getType(),
rs,
isCancellationRequest);
searchResults.add(order);
itemNb++;
} //multipage management
while (rs.next() && (itemNb < pPageSize));
pReply.setTotalNumberOfLines(cstmt.getInt(11));
}
}
} finally {

closeConnection(connection, cstmt, rs, pSession);

}

return searchResults;
}

Environment : Oracle 9.2.0.6, JDBC driver thin or OCI 9.2.0.6, Weblogic 6.1SP3 running JDK 1.3.1_03-b03 on Solaris (SunOS 5.8 Generic_117000-03 sun4u sparc SUNW,Sun-Fire-V440) / SPARC.
Weblogic is used in Cluster mode : 6 servers + 1 hosting JMS Queues on the first machine, 2 servers on a second machine.
The error appears inside a Session EJB with transaction type "NotSupported", with DB connection taken from a non-XA Tx Datasource.

We have already unsuccessfully tested the following items :
- OCI and thin driver 9.2.0.6 : both report the same error
- set defaultRowPrefetch=1000 in the Connection Pool properties (1000 is supposed to be greater than the maximum number of rows fetched from the ResultSet)
- do a "setFetchSize(16000)" call on the Statement after creation
- run with thin driver 9.0.1.4
- put connection pool size to min:15, max:15 (15 is the number of threads of the default Execution Queue)

Apparently, this is potentially happening on any "getObject" call returning a cursor, and may be linked to another error that we encountered previously during XA transactions
(again only under load conditions). At that time we were testing with JDBC driver 9.0.1.1.0. This error seems to have disapeared when switching to driver 9.2.0.6. It was :

7 Dec 2004 12:17:15.991;-9201346976409387008;class com.cs.tnp.automaton.ejb.OrderAutomatonMDB;null;VESTATOI20000;java.sql.SQLException: Not in a transaction
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:285)
at oracle.jdbc.driver.OracleConnection.needLine(OracleConnection.java:2015)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:243)
at weblogic.jdbc.rmi.internal.ResultSetImpl.next(ResultSetImpl.java:133)
at com.cs.tnp.datamodel.sec.CurrencyFactory.getCurrencyList(Unknown Source)
at com.cs.tnp.datamodel.part.psc.PSCFactory.setAcceptedCurrenciesList(Unknown Source)
at com.cs.tnp.datamodel.part.psc.PSCFactory.getByParticipantIdsAndCurrency(Unknown Source)
at com.cs.tnp.datamodel.part.psc.PSCFactory.getByOHAParticipantRoleIdsAndFundRefIdPerCurrency(Unknown Source)
at com.cs.tnp.datamodel.op.sett.resolution.SettlementResolutionBehaviour.resolveSettlement(Unknown Source)
at com.cs.tnp.datamodel.op.order.OrderBehaviour.resolveSettlement(Unknown Source)
at com.cs.tnp.datamodel.op.order.OrderBehaviour.isValid(Unknown Source)
at com.cs.tnp.automaton.servicedelegate.op.order.ServiceDelegateOrderCreation.execute(Unknown Source)
at com.cs.tnp.automaton.action.CallServiceStateAction.processEvent(Unknown Source)
at com.cs.tnp.automaton.action.CallServiceStateAction.handleEvent(Unknown Source)
at com.cs.tnp.automaton.state.MultipleEventActionState.processEventImpl(Unknown Source)
at com.cs.tnp.automaton.state.AbstractState.processEvent(Unknown Source)
at com.cs.tnp.automaton.activity.AbstractActivity.processEvent(Unknown Source)
at com.cs.tnp.automaton.core.OrderAutomaton.processMessageImpl(Unknown Source)
at com.cs.tnp.automaton.core.AbstractAutomaton.processMessage(Unknown Source)
at com.cs.tnp.automaton.SuperMDBAutomaton.onMessageDirect(Unknown Source)
at com.cs.tnp.automaton.SuperMDBAutomaton.onMessage(Unknown Source)
at weblogic.ejb20.internal.MDListener.execute(MDListener.java:262)
at weblogic.ejb20.internal.MDListener.onMessage(MDListener.java:214)
at weblogic.jms.client.JMSSession.onMessage(JMSSession.java:1865)
at weblogic.jms.client.JMSSession.execute(JMSSession.java:1819)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:139)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:120)

Does someone have an idea where this could come from ?

Joe Weinstein

unread,
Dec 15, 2004, 12:20:29 PM12/15/04
to

Nicolas Strauel wrote:

> As we start response time testing, we encounter the following issue when connecting approximately 6 users simultaneously, doing the same query with no think time :
>
> java.sql.SQLException: ORA-01002: fetch out of sequence
>
> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
> at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2355)
> at oracle.jdbc.oci8.OCIDBAccess.fetch(OCIDBAccess.java:2028)
> at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:534)
> at oracle.jdbc.driver.OracleStatement.getCursorValue(OracleStatement.java:3899)
> at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:6104)
> at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5892)
> at oracle.jdbc.driver.OracleCallableStatement.getObject(OracleCallableStatement.java:707)
> at weblogic.jdbc.pool.Statement.getObject(Statement.java:745)
> at weblogic.jdbc.rmi.internal.CallableStatementImpl.getObject(CallableStatementImpl.java:184)
> at weblogic.jdbc.rmi.SerialCallableStatement.getObject(SerialCallableStatement.java:291)
> at com.cs.tnp.tools.access.db.FLKCallableStatementWrapper.getObject(Unknown Source)

Hi. My first suspicion is to investigate whether every thread is guaranteed to
use a different connection.
Joe

Nicolas Strauel

unread,
Dec 16, 2004, 2:55:21 AM12/16/04
to
OK, but how do I ensure this ?
Our DB connections are obtained inside EJBs and MDBs using a Datasource. We do not control threads or "actual" creation/allocation of the connections...

Joe Weinstein

unread,
Dec 16, 2004, 11:28:08 AM12/16/04
to

Nicolas Strauel wrote:

I suspect the code in at com.cs.tnp.tools.access.db.FLKCallableStatementWrapper.getObject()
Your EJBs and MDBs are used simultaneously by multiple threads. If the above
code is written so more than one thread will be using the same JDBC object, you cause
a problem. Every JDBC object should be *defined* and created in the method that is going
to use it, and closed without fail by the end of the method. If you have a Connection
or Statement defined outside a method, it can be inadvertantly shared.
Joe

Nicolas Strauel

unread,
Dec 21, 2004, 2:23:39 AM12/21/04
to
Actually, we already do this, as in the following example :

Connection connection = null;
CallableStatement cstmt = null;
ResultSet rs = null;

try {
// accessing connection
connection = getConnection();
...
cstmt = connection.prepareCall(PS_GET_BUSMSGGRP_BY_FUNC_REF);
...
cstmt.setInt(1, pIdConverted);
cstmt.setString(2, pFunctionalReference);
cstmt.registerOutParameter(3, java.sql.Types.OTHER);
cstmt.execute();

// reading the message log
rs = (ResultSet) cstmt.getObject(3);
if (!rs.next()) {
...
} else {
...


} finally {
closeConnection(connection, cstmt, rs, pSession);
}

Every DB-access codes are written this way.
I have also checked the "static" members around Connection, Statement and other DB-related objects : there is none of them, apart from the Datasource itself.

We have switched to the Weblogic driver for Oracle : according to first tests, it completely solves the problem (still to be confirmed).
In that case, it would appear that there is a major issue in the Oracle driver itself. I highly suspect this appears in the specific case where we fetch in Java a Cursor that is opened in PL/SQL and then returned back as a Java ResultSet.

pradeep tiwari

unread,
Dec 22, 2004, 8:59:05 AM12/22/04
to
Hi,
According to me first you should set connection setAutoCommit mode false and then execute the query,because by default setAutoCommit is true in JDBC and if you are opening a cursor in first query and try to use this cursor in second query it generates the same fetch out of sequence error.So do this and try..
thanks

Nicolas Strauel

unread,
Dec 23, 2004, 5:46:30 AM12/23/04
to

You are right.
We tried it already some days ago.
It did not change anything.

0 new messages