Hi all,
First, I'm hoping this is the appropriate forum for questions about the Oracle's JDBC driver for Oracle Database. The title/hierarchy of the forum makes me think it is for JDBC in general, but I see plenty of other ojdbc questions here, so I'll proceed.
So, I am running into a ORA-01002 ("fetch out of sequence") problem. My code is reading ~300,000 rows from a query, and inserting data into another system. I commit the transaction every 10,000 rows. This used to work just fine, but I started running into this problem (I think) when I moved to jboss AS6 to jboss AS7. I am looking for advice from ojdbc experts on what sort of thing AS7 might possibly be doing to the driver that would trigger this.
I have read in several places that you will get an ORA-01002 if you run a 'Select for update' query and commit part way through your iteration. I am using a simple 'Select' query, though. I suspect some ojdbc property or setting is being activated that makes my query act as if it was a 'select for update' query, but I don't know of any such setting.
I was able to simplify my query/code into a small test case (below). When I run this code outside of the appserver, using a straight OracleXADataSource and a UserTransaction mock (that simply calls connection.commit()), the test runs fine. No ORA-01002. However, when I run this code in the appserver, I get an exception when executing the read(1) line (stacktrace below).
Does anyone have thoughts on what is going on here?
Thanks for your help,
Matt Drees
Test case:
@Name("fetchTest")
@AutoCreate
public class FetchTest
{
@In DataSource peoplesoftDatasource;
@In(create = true) UserTransaction transaction;
private ResultSet resultSet;
public void test() throws Exception
{
Connection connection = peoplesoftDatasource.getConnection();
Statement statement = connection.createStatement();
statement.setFetchSize(5);
/** generates 20 rows */
resultSet = statement.executeQuery("select level from dual connect by level < 20");
read(5);
transaction.commit();
read(1);
}
private void read(int numberOfRowsToRead) throws SQLException
{
for (int i = 0; i < numberOfRowsToRead; i++)
{
resultSet.next();
}
}
}
ORA-01002 Stack Trace:
(... several nonrelevant stack frames removed...)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:952) [jbossweb-7.0.1.Final.jar:7.0.1.Final]
at java.lang.Thread.run(Thread.java:680) [:1.6.0_26]
Caused by: javax.faces.el.EvaluationException: java.sql.SQLException: ORA-01002: fetch out of sequence
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:102) [jboss-jsf-api_2.0_spec-1.0.0.Final.jar:1.0.0.Final]
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102) [jsf-impl-2.0.4-b09-jbossorg-4.jar:2.0.4-b09-jbossorg-4]
... 62 more
Caused by: java.sql.SQLException: ORA-01002: fetch out of sequence
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:452) [ojdbc6_g-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400) [ojdbc6_g-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:884) [ojdbc6_g-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:471) [ojdbc6_g-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:199) [ojdbc6_g-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:535) [ojdbc6_g-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:197) [ojdbc6_g-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1469) [ojdbc6_g-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:722) [ojdbc6_g-11.2.0.2.0.jar:11.2.0.2.0]
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:596) [ojdbc6_g-11.2.0.2.0.jar:11.2.0.2.0]
at org.jboss.jca.adapters.jdbc.WrappedResultSet.next(WrappedResultSet.java:1840)
at org.ccci.ccp.pages.admin.FetchTest.read(FetchTest.java:52) [classes:]
at org.ccci.ccp.pages.admin.FetchTest.test(FetchTest.java:45) [classes:]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [:1.6.0_26]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [:1.6.0_26]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [:1.6.0_26]
at java.lang.reflect.Method.invoke(Method.java:597) [:1.6.0_26]
at org.jboss.seam.util.Reflections.invoke(Reflections.java:22) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:32) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:77) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:185) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:103) [jboss-seam-2.2.2.Final.seam2jsf2-no-timer-service-dispatcher.jar:]
at org.ccci.ccp.pages.admin.FetchTest_$$_javassist_53.test(FetchTest_$$_javassist_53.java) [classes:]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [:1.6.0_26]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [:1.6.0_26]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [:1.6.0_26]
at java.lang.reflect.Method.invoke(Method.java:597) [:1.6.0_26]
at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:335) [jboss-el-1.0_02.CR5.jar:]
at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:348) [jboss-el-1.0_02.CR5.jar:]
at org.jboss.el.parser.AstPropertySuffix.invoke(AstPropertySuffix.java:58) [jboss-el-1.0_02.CR5.jar:]
at org.jboss.el.parser.AstValue.invoke(AstValue.java:96) [jboss-el-1.0_02.CR5.jar:]
at org.jboss.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276) [jboss-el-1.0_02.CR5.jar:]
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105) [jsf-impl-2.0.4-b09-jbossorg-4.jar:2.0.4-b09-jbossorg-4]
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88) [jboss-jsf-api_2.0_spec-1.0.0.Final.jar:1.0.0.Final]
... 63 more