Announcement Announcement Module
Collapse
No announcement yet.
ORA-01002: fetch out of sequence error Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ORA-01002: fetch out of sequence error

    I am getting a java.sql.SQLException: ORA-01002: fetch out of sequence while trying to do a refresh, basically select for update? How do I set autocommit to false? Any help is appreciated.

    byte[] buffer = new byte[1];
    buffer[0] = 1;
    email.setEmailattachment(Hibernate.createBlob(buff er));
    Long id = (Long)session.save(email);
    System.out.println("id " + id);

    getHibernateTemplate().refresh(email, LockMode.UPGRADE); //error here..

  • #2
    You must set autcommit to false on your connection pool/datasource.

    Comment


    • #3
      ORA-01002: fetch out of sequence

      Thanks for your reply. How do I set autocommit to false in Spring/Hibernate?

      Comment


      • #4
        It depends on the datasource or pool. For example, with dbcp, there is an option described here:

        http://jakarta.apache.org/commons/db...iguration.html

        Comment


        • #5
          ORA-01002: fetch out of sequence

          Thanks for your reply. I dont want to make any global settings to set the autocommit to false. I would like to do something within the method where I would be making a call to "getHibernateTemplate().refresh(email, LockMode.UPGRADE);".

          Like would like to something like this:
          setAutoCommit(false);
          getHibernateTemplate().refresh(email, LockMode.UPGRADE);
          setAutoCommit(true);

          How do I accomplish this using spring/hibernate?

          Thanks for your help.

          Comment


          • #6
            I should mention that for DataSourceTrnasactionManager, Spring automatically sets autocommit to false, when you enter a transaction, and for HibernateTransactionManager, Hibernate iteself does it for the same net effect. For JTA situations, most JTA Datasource providers do the same thing.

            Are you sure you're even in a transaction?

            Comment


            • #7
              ORA-01002: fetch out of sequence

              we do use HibernateTransactionManager for transaction.

              <!-- Transaction manager for a single Hibernate SessionFactory
              <bean id="myTransactionManager" class="org.springframework.orm.hibernate.Hibernate TransactionManager">
              <property name="sessionFactory"><ref local="mySessionFactory"/></property>
              </bean>

              Comment


              • #8
                If you turn up your logging so you see debug messages, you should see the logging from Hibernate that it is actually turning off the auto-commit.

                You might want to look at the log to verify that you are in fact in a transaction, and then Hibernate is actually turning off the auto-commit. If both of these are true, then I don't think you're getting the out-of-sequence error because of the auto-commit setting.

                Comment


                • #9
                  ORA-01002: fetch out of sequence

                  Below is the stacktrace in the log file.

                  313 [26 Aug 2004 10:32:37,156] [main] WARN org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator - Unable to translate SQLException with errorCode '6502', will now try the fallback translator
                  0 [26 Aug 2004 10:58:28,890] [main] DEBUG com.xxx.util.SpringBeanLocator - bean factory created
                  16 [26 Aug 2004 10:58:28,906] [main] DEBUG com.xxx.persistence.DAOLocator - DAO locator created
                  31 [26 Aug 2004 10:58:28,921] [main] DEBUG com.xxx.dao.hibernate.EmailDAOHibernateImpl - save email
                  188 [26 Aug 2004 10:58:29,078] [main] DEBUG net.sf.hibernate.SQL - select hibernate_sequence.nextval from dual
                  297 [26 Aug 2004 10:58:29,187] [main] DEBUG net.sf.hibernate.SQL - insert into EMAILS (EMAILFROM, EMAILTO, EMAILBODY, EMAILSUBJECT, EMAILATTACHMENT, EMAIL_ID) values (?, ?, ?, ?, ?, ?)
                  328 [26 Aug 2004 10:58:29,218] [main] DEBUG net.sf.hibernate.SQL - select EMAIL_ID, EMAILFROM, EMAILTO, EMAILBODY, EMAILSENT, EMAILCC, EMAILBCC, EMAILSUBJECT as EMAILSUB8_, SMTPHOST, EMAILATTACHMENT as EMAILAT10_, EMAILATTACHMENTTYPE as EMAILAT11_, EMAILATTACHMENTFILENAME as EMAILAT12_ from EMAILS where EMAIL_ID =? for update
                  360 [26 Aug 2004 10:58:29,250] [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 1002, SQLState: 72000
                  360 [26 Aug 2004 10:58:29,250] [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - ORA-01002: fetch out of sequence

                  375 [26 Aug 2004 10:58:29,265] [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 1002, SQLState: 72000
                  375 [26 Aug 2004 10:58:29,265] [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - ORA-01002: fetch out of sequence

                  375 [26 Aug 2004 10:58:29,265] [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - could not load: [com.xxx.bean.Email#127]
                  java.sql.SQLException: ORA-01002: fetch out of sequence

                  at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
                  at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89)
                  at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
                  at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1891)
                  at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.j ava:1198)
                  at oracle.jdbc.driver.OracleStatement.doExecuteQuery( OracleStatement.java:2400)
                  at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:2672)
                  at oracle.jdbc.driver.OraclePreparedStatement.execute Update(OraclePreparedStatement.java:589)
                  at oracle.jdbc.driver.OraclePreparedStatement.execute Query(OraclePreparedStatement.java:527)
                  at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeQuery(DelegatingPreparedStatement.java:91 )
                  at net.sf.hibernate.impl.BatcherImpl.getResultSet(Bat cherImpl.java:87)
                  at net.sf.hibernate.loader.Loader.getResultSet(Loader .java:875)
                  at net.sf.hibernate.loader.Loader.doQuery(Loader.java :269)
                  at net.sf.hibernate.loader.Loader.doQueryAndInitializ eNonLazyCollections(Loader.java:133)
                  at net.sf.hibernate.loader.Loader.loadEntity(Loader.j ava:911)
                  at net.sf.hibernate.loader.Loader.loadEntity(Loader.j ava:931)
                  at net.sf.hibernate.loader.SimpleEntityLoader.load(Si mpleEntityLoader.java:58)
                  at net.sf.hibernate.persister.EntityPersister.load(En tityPersister.java:419)
                  at net.sf.hibernate.impl.SessionImpl.refresh(SessionI mpl.java:2183)
                  at org.springframework.orm.hibernate.HibernateTemplat e$7.doInHibernate(HibernateTemplate.java:259)
                  at org.springframework.orm.hibernate.HibernateTemplat e.execute(HibernateTemplate.java:176)
                  at org.springframework.orm.hibernate.HibernateTemplat e.refresh(HibernateTemplate.java:257)
                  at com.csc.exes.dao.hibernate.EmailDAOHibernateImpl.s ave(EmailDAOHibernateImpl.java:82)
                  at com.csc.exes.dao.hibernate.EmailDAOHibernateImplTe st.testSave(EmailDAOHibernateImplTest.java:52)
                  at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
                  at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
                  at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
                  at java.lang.reflect.Method.invoke(Method.java:324)
                  at junit.framework.TestCase.runTest(TestCase.java:154 )
                  at junit.framework.TestCase.runBare(TestCase.java:127 )
                  at junit.framework.TestResult$1.protect(TestResult.ja va:106)
                  at junit.framework.TestResult.runProtected(TestResult .java:124)
                  at junit.framework.TestResult.run(TestResult.java:109 )
                  at junit.framework.TestCase.run(TestCase.java:118)
                  at junit.framework.TestSuite.runTest(TestSuite.java:2 08)
                  at junit.framework.TestSuite.run(TestSuite.java:203)
                  at junit.textui.TestRunner.doRun(TestRunner.java:116)
                  at com.intellij.rt.execution.junit2.IdeaJUnitAgent.do Run(IdeaJUnitAgent.java:57)
                  at junit.textui.TestRunner.start(TestRunner.java:172)
                  at com.intellij.rt.execution.junit.TextTestRunner2.st artRunnerWithArgs(TextTestRunner2.java:23)
                  at com.intellij.rt.execution.junit2.JUnitStarter.prep areStreamsAndStart(JUnitStarter.java:97)
                  at com.intellij.rt.execution.junit2.JUnitStarter.main (JUnitStarter.java:31)
                  at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
                  at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
                  at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
                  at java.lang.reflect.Method.invoke(Method.java:324)
                  at com.intellij.rt.execution.application.AppMain.main (AppMain.java:78)

                  Comment


                  • #10
                    You haven't included enough of the log to see any of the transaction stuff from Spring, or the autocommit modification, from Hibernate...

                    Comment


                    • #11
                      ORA-01002: fetch out of sequence

                      I guess I am not very clear with the transactions concept yet with spring/hibernate. what are all the different procedures to set up transactions?

                      I thought getHibernateTemplate() takes care of Transaction tx = s.beginTransaction(); and all that? Do I need any explicit code to do this?

                      Need guidance.

                      Thanks.

                      Comment


                      • #12
                        HibernateTemplate just handles Hibernate Session management, and will coordinate with an outer wrapping transaction, in terms of using any session that is already bound to the current thread and transaction, or depending on how it it set up, even creating a new session (which is what is happening for you).

                        It does not do any creation or management of transactions itself. You need to set up transactions in a declarative or programmatic fashion. Please read the Spring documentation and look at the samples. There is extensive documentation on transaction handling in Spring, with and without Hibernate, and a decent amount of samples come with Spring which show the stuff in action.

                        Comment


                        • #13
                          session.flush()

                          I have transaction defined as below in my application context file.
                          <!-- Hibernate Transaction Manager -->
                          <bean id="myTransactionManager" class="org.springframework.orm.hibernate.Hibernate TransactionManager">
                          <property name="sessionFactory">
                          <ref local="mySessionFactory"/>
                          </property>
                          </bean>

                          <!-- Transaction Proxy Template -->
                          <bean id="txProxyTemplate" lazy-init="true"
                          class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
                          <property name="transactionManager">
                          <ref local="myTransactionManager"/>
                          </property>
                          <property name="transactionAttributes">
                          <props>
                          <prop key="*">PROPAGATION_REQUIRED</prop>
                          </props>
                          </property>
                          </bean>
                          <bean id="myEmailDAO" parent="txProxyTemplate">
                          <property name="target">
                          <ref local="myEmailTarget"/>
                          </property>
                          <property name="transactionAttributes">
                          <props>
                          <prop key="save*">PROPAGATION_REQUIRED,-Exception</prop>
                          </props>
                          </property>
                          </bean>
                          <bean id="myEmailTarget" class="com.xxx.hibernate.EmailDAOHibernateImpl" parent="myGenericDAO"/>

                          My code:
                          Session s = this.getSession();
                          try {
                          File attachment = new File("C:/temp/full_cream.gif");
                          InputStream is = new FileInputStream(attachment);
                          byte[] data = new byte[is.available()];
                          is.read(data);
                          is.close();

                          byte[] test = {1};
                          email.setEmailattachment(Hibernate.createBlob(test ));
                          getHibernateTemplate().save(email);
                          s.flush();
                          getHibernateTemplate().refresh(email, LockMode.UPGRADE);

                          OutputStream out = ((oracle.sql.BLOB) email.getEmailattachment()).getBinaryOutputStream( );
                          out.write(data);
                          out.close();

                          If I dont have s.flush() in my code, I get an exception:
                          Caused by: org.springframework.orm.hibernate.HibernateSystemE xception: this instance does not yet exist as a row in the database; nested exception is net.sf.hibernate.HibernateException: this instance does not yet exist as a row in the database.

                          I thought spring handles flush() internally?? what am I missing??

                          Thanks.

                          Comment


                          • #14
                            You wil get an automatic flush when the transaction ends. You of course also get a flush when the Session is closed, which in most usage scenarios will be the same lifetime as the transaction. Hibernate itself will decide to flush when you call certain Hibernate code, such as a query for example...

                            You can either manually flush as you are doing, or you can also change the flush strategy in HibernateTemplate. You can for example set it to FLUSH_EAGER. However, that's going to force a flush after every operation, and is normally not recommended, as it will kill performance in most scenarios. The whole point is that you generally want to rely on Hibernate's session cache to speed things up. BTW, the need to flush and sync up when handling blobs is actually mentioned in the Hibernate docs or some of the Oracle specific stuff on the Hibernate wiki. I remember seeing a note about this. Finally, don't forget that Spring does have some LOB convenience classes, basically implementations of the LobHandler interface, such that you don't have to have Oracle specific code like you are using now...

                            Comment

                            Working...
                            X