ORA-01002 Fetch out of sequence oracle when fetching 30 Million records

Question

I am trying to fetch 30 million records through Inputstream.read() suggested in Java SQL Result to InputStream. I'll be processing those records and transfering with GFT. when trying this process, I am getting java.sql.SQLException: ORA-01002: fetch out of sequence error on my resultset. but, it's not happening everytime. I see few success with 30,20 million records. sometimes, i get this error for 15 million records too. Out of sequence thrown after reading some records(not at beginning)

Following is how i am creating my connection & resultset

connection = dataSource.getConnection();
sql = "select query" // I am selecting 30 columns from table
stmt = connection.prepareStatement(sql); //using PreparedStatement
stmt.fetchSize() // It was set to 300.
rs = stmt.executeQuery(); //resultset
//Not overriding anymore default properties

Following are the properties in my config

spring.datasource.type: com.zaxxer.hikari.HikariDataSource
spring.datasource.driver-class-name: oracle.jdbc.OracleDriver

My oracle version: Oracle Database 12c Enterprise Edition Release

Is there anything i need to override the default connection/stmt/resultset properties? I am not sure, if it's related to connection. I am not able simulate/find out pattern when this was happening.

Query used was SELECT COLUMN_1, COLUMN_2, COLUMN_3....COLUMN_30 FROM TABLE_NAME where TIMESTAMP < ? and TIMESTAMP >= ? provided timestamps after creating preparedStatement stmt.setTimestamp(1, timestamp); stmt.setTimestamp(2, timestamp);

I was able to debug this once. onething i observed is resultset.next() is throwing this fetch out of sequence error. when i expand delegate object inside the resultset, observed fields are

isAllFetched = false
isDoneFetchingRows = false
currentRow = 7035999
fetchedrowCount = 7036000

This is the point where resultset.next() throwing Fetch out of sequence

Please show your exact query. Does it use PL/SQL functions in it? — Sayan Malakshinov, Jul 07 '21 at 00:03
@SayanMalakshinov updated the post. Not using PL/SQL functions. — user16353001, Jul 07 '21 at 00:20
Are you doing any update for the rows and how frequently are you committing the rows , if yes you can remove the Commit and commit only once for all the rows. Can the update that you are doing in the for loop can it be done by a single SQL statement . — Himanshu Kandpal, Jul 07 '21 at 00:36
I am not doing update on any of the rows and just reading all the columns for each row. I think auto-commit is set to true by default, does that effect me when not doing any update? @hkandpal — user16353001, Jul 07 '21 at 00:41
just enable SQL_TRACE (`alter session set sql_trace=TRUE`), repeat the error and provide your raw trace file here. — Sayan Malakshinov, Jul 07 '21 at 00:43
Are you sure you're detecting the end of fetch properly, and not trying to fetch rows after the last one been fetched? This sometimes happens when you have an issue while looping on the result set and it is not correctly handled. — gsalem, Jul 07 '21 at 07:30
@gsalem yes, i think so. and it's not happening every time we read the db records(30 million). only sometime it throws the error. I never seen this error when fetching less records(like 1-5 million). — user16353001, Jul 07 '21 at 16:07
I am using only select in my sql query, does auto-commit effect that? — user16353001, Jul 21 '21 at 14:06

ORA-01002 Fetch out of sequence oracle when fetching 30 Million records

0 Answers0