This discussion is archived
4 Replies Latest reply: Aug 29, 2011 9:01 AM by 644842 RSS

ORA-01002 ("fetch out of sequence") without 'for update' cluase, only in a

644842 Newbie
Currently Being Moderated
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
  • 1. Re: ORA-01002 ("fetch out of sequence") without 'for update' cluase, only in a
    Joe Weinstein Expert
    Currently Being Moderated
    I would try to reduce it to pure JDBC, including whether/how the transaction is started and committed.
    The cursor for the query is not surviving the end of the transaction, and this is a known behavior. I'll
    bet if you set the fetch size to 25, it'll work.
  • 2. Re: ORA-01002 ("fetch out of sequence") without 'for update' cluase, only in a
    644842 Newbie
    Currently Being Moderated
    Thanks for the response, Joe.

    It's hard to reduce my real problem to pure JDBC, since I'm reading from one database and writing to another, and therefore need a transaction manager involved.

    I'd like to know exactly what conditions would cause a cursor to die at the end of a transaction; clearly it sometimes survives (or my pure test case above would always fail, instead of only when run inside the appserver).


    Also, to address your point on increasing the fetch size, I'd agree that if I could make my fetch size large enough, it'd solve my problem, but I don't have enough memory to increase it past 300,000.
  • 3. Re: ORA-01002 ("fetch out of sequence") without 'for update' cluase, only in a
    Joe Weinstein Expert
    Currently Being Moderated
    OK, understood. I am swamped with work, but if you can google for oracle keeping cursors open after transactions, maybe you'll find the answer before me.
  • 4. Re: ORA-01002 ("fetch out of sequence") without 'for update' cluase, only in a
    644842 Newbie
    Currently Being Moderated
    Thanks for your help anyway, Joe.


    I did some more research, and found this statement at http://download.oracle.com/docs/cd/A58617_01/server.804/a58241/ch_xa.htm:
    "When used in an Oracle XA application, cursors are valid only for the duration of the transaction"

    This isn't in reference to JDBC, and it's from old documentation, but I wanted to test if this was the case still. I tweaked my test to use a distributed transaction driven by a simplistic transaction manager i put together based on code from http://download.oracle.com/docs/cd/E11882_01/java.112/e16548/xadistra.htm#JJDBC28000. My new test case is below. This test fails, now, with an ORA-01002 error.

    So it appears that cursors are in fact always closed after an XA transaction completes. Bummer. :-(

    I must have somehow been using a non-XA transaction before, when my original code was working fine.


    Hopefully this will help someone else down the road.

    package org.ccci.ccp.pages.admin;
    
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.Random;
    
    import javax.sql.DataSource;
    import javax.sql.XAConnection;
    import javax.transaction.HeuristicMixedException;
    import javax.transaction.HeuristicRollbackException;
    import javax.transaction.NotSupportedException;
    import javax.transaction.RollbackException;
    import javax.transaction.SystemException;
    import javax.transaction.UserTransaction;
    import javax.transaction.xa.XAException;
    import javax.transaction.xa.XAResource;
    import javax.transaction.xa.Xid;
    
    import oracle.jdbc.xa.OracleXid;
    import oracle.jdbc.xa.client.OracleXADataSource;
    
    import org.ccci.util.NotImplementedException;
    import org.testng.annotations.Test;
    
    import com.google.common.base.Throwables;
    
    public class FetchTestTest
    {
        
        public class SimpleXaTransaction implements UserTransaction
        {
    
            private final ConnectionTrackingDataSource ds;
            private Xid xid;
            private XAResource xaResource;
    
            public SimpleXaTransaction(ConnectionTrackingDataSource ds)
            {
                this.ds = ds;
            }
    
            @Override
            public void begin()
                throws NotSupportedException, SystemException
            {
                try
                {
                    if (xaResource == null)
                    {
                        XAConnection xaConnection = ds.delegate.getXAConnection();
                        ds.xaConnection = xaConnection;
                        xaResource = xaConnection.getXAResource();
                    }
                    
                    xid = createXid();
                    xaResource.start(xid, XAResource.TMNOFLAGS);
                }
                catch (Exception e)
                {
                    throw Throwables.propagate(e);
                }
            }
            
            Random random = new Random();
    
            private Xid createXid() throws XAException
            {
                return new OracleXid(0, createSmallId(), createSmallId());
            }
    
            private byte[] createSmallId()
            {
                return (String.valueOf(random.nextInt(1000))).getBytes();
            }
    
            @Override
            public void commit()
                throws RollbackException, HeuristicMixedException, HeuristicRollbackException, SecurityException,
                IllegalStateException, SystemException
            {
                try
                {
                    xaResource.end(xid, XAResource.TMSUCCESS);
                    int prepareOutput = xaResource.prepare(xid);
                    if (prepareOutput == XAResource.XA_OK)
                    {
                        xaResource.commit(xid, false);
                    }
                    else if (prepareOutput != XAResource.XA_RDONLY)
                    {
                        xaResource.rollback(xid);
                    }
                }
                catch (XAException e)
                {
                    throw Throwables.propagate(e);
                }
            }
    
            @Override
            public int getStatus()
                throws SystemException
            {
                throw new NotImplementedException();
            }
    
            @Override
            public void rollback()
                throws IllegalStateException, SecurityException, SystemException
            {
                try
                {
                    xaResource.rollback(xid);
                }
                catch (XAException e)
                {
                    throw Throwables.propagate(e);
                }
            }
    
            @Override
            public void setRollbackOnly()
                throws IllegalStateException, SystemException
            {
                throw new NotImplementedException();
            }
    
            @Override
            public void setTransactionTimeout(int arg0)
                throws SystemException
            {
                throw new NotImplementedException();
            }
    
        }
    
        class ConnectionTrackingDataSource implements DataSource
        {
            public XAConnection xaConnection;
            OracleXADataSource delegate;
            private Connection connection;
            
            public ConnectionTrackingDataSource(OracleXADataSource delegate)
            {
                this.delegate = delegate;
            }
    
            public PrintWriter getLogWriter()
                throws SQLException
            {
                return delegate.getLogWriter();
            }
    
            public <T> T unwrap(Class<T> iface)
                throws SQLException
            {
                return delegate.unwrap(iface);
            }
    
            public void setLogWriter(PrintWriter out)
                throws SQLException
            {
                delegate.setLogWriter(out);
            }
    
            public boolean isWrapperFor(Class<?> iface)
                throws SQLException
            {
                return delegate.isWrapperFor(iface);
            }
    
            public void setLoginTimeout(int seconds)
                throws SQLException
            {
                delegate.setLoginTimeout(seconds);
            }
    
            public Connection getConnection()
                throws SQLException
            {
                if (connection == null)
                {
                    connection = xaConnection.getConnection();
                }
                return connection;
            }
    
            public Connection getConnection(String username, String password)
                throws SQLException
            {
                return delegate.getConnection(username, password);
            }
    
            public int getLoginTimeout()
                throws SQLException
            {
                return delegate.getLoginTimeout();
            }
            
    
    
        }
    
        @Test
        public void test() throws Exception
        {
            FetchTest test = new FetchTest();
            
            OracleXADataSource oracleDataSource = createXADatasource();
            ConnectionTrackingDataSource ds = new ConnectionTrackingDataSource(oracleDataSource);
            
            test.peoplesoftDatasource = ds;
            
            test.transaction = new SimpleXaTransaction(ds);
            
            test.transaction.begin();
            try
            {
                test.test();
                test.transaction.commit();
            }
            catch (Exception e)
            {
                test.transaction.rollback();
                throw e;
            }
            
            
            ds.getConnection().close();
        }
    
        private OracleXADataSource createXADatasource() throws SQLException
        {
            String url = "jdbc:oracle:thin:@...";
            String username = "...";
            String password = "...";
            
            OracleXADataSource ds = new OracleXADataSource();
            ds.setURL(url);
            ds.setUser(username);
            ds.setPassword(password);
            return ds;
        }
    
    }

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points