Exception ORA-08103: object no longer exists on using setfetchsize of Hibernate

I'm using Hibernate. I need to fetch around 1000000 records and it will cause timeout exception. So I'm using setfetchsize for 6000 records, so that it will distribute the operation in multiple transactions each of 6000 records.

It will take around 21 hours to fetch all.

But meanwhile retrieving records if somebody deletes one of the record which was to be fetched then I get ORA-08103: object no longer exists.

Now I want to skip that object which is deleted while retrieving. How can I do this?

2 Answers

Most likely that a cursor is opened based on a global temporary table(GTT), which had been created with ON COMMIT DELETE ROWS option. And the cause of the ORA-08103: object no longer exists error is commit statement that followed right after the delete statement. Here is a simple example:

 SQL> declare 2 type t_recs is table of number; 3 l_cur sys_refcursor; -- our cursor 4 l_rec t_recs; 5 6 begin 7 8 -- populating a global temporary table GTT1 with sample data 9 insert into GTT1(col) 10 select level 11 from dual 12 connect by level <= 1000; 13 14 open l_cur -- open a cursor based on data from GTT1 15 for select col 16 from GTT1; 17 18 -- here goes delete statement 19 -- and 20 commit; <-- cause of the error. After committing all data from GTT1 will be 21 -- deleted and when we try to fetch from the cursor 22 loop -- we'll face the ORA-08103 error 23 fetch l_cur -- attempt to fetch data which are long gone. 24 bulk collect into l_rec; 25 exit when l_cur%notfound; 26 end loop; 27 28 end; 29 /
ORA-08103: object no longer exists
ORA-06512: at line 24

Recreation of global temporary table with on commit preserve rows clause will allow to safely fetch data from a cursor that is based on that table without being afraid of facing ORA-08103: error.

After Struggling one week finally I fixed the Issue:

Solution: Most likely that a cursor is opened based on a global temporary table(GTT), which had been created with ON COMMIT DELETE ROWS option. And the cause of the ORA-08103: object no longer exists error is commit statement that followed right after the delete statement. DBA team didn't agree to change the GTT as on commit preserve rows so finally I added code base in Java Service Layer[Implementing the Spring - Programmatic Transaction]

package com.test;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
public class StudentJDBCTemplate implements StudentDAO { private DataSource dataSource; private JdbcTemplate jdbcTemplateObject; private PlatformTransactionManager transactionManager; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; this.jdbcTemplateObject = new JdbcTemplate(dataSource); } public void setTransactionManager(PlatformTransactionManager transactionManager) { this.transactionManager = transactionManager; } public void create(String name, Integer age, Integer marks, Integer year){ TransactionDefinition def = new DefaultTransactionDefinition(); TransactionStatus status = transactionManager.getTransaction(def); try { String SQL1 = "insert into Student (name, age) values (?, ?)"; jdbcTemplateObject.update( SQL1, name, age); // Get the latest student id to be used in Marks table String SQL2 = "select max(id) from Student"; int sid = jdbcTemplateObject.queryForInt( SQL2 ); String SQL3 = "insert into Marks(sid, marks, year) " + "values (?, ?, ?)"; jdbcTemplateObject.update( SQL3, sid, marks, year); System.out.println("Created Name = " + name + ", Age = " + age); transactionManager.commit(status); } catch (DataAccessException e) { System.out.println("Error in creating record, rolling back"); transactionManager.rollback(status); throw e; } return; } public List<StudentMarks> listStudents() { String SQL = "select * from Student, Marks where Student.id=Marks.sid"; List <StudentMarks> studentMarks = jdbcTemplateObject.query(SQL, new StudentMarksMapper()); return studentMarks; }
}