java.sql.SQLException: ORA-01410: invalid ROWID

Question

I am getting java.sql.SQLException: ORA-01410: invalid ROWID for some of the rows.

Here is my query.

"select OP.PLU_LONG PROD_ID," +
                          "OP.NEW_PRICE PRICE," +
                          "OP.REG_PRICE REGPRICE," +
                          "OP.QUANTITY_LIMIT QUANTITY_LIMIT," +
                          "OP.ON_AD_DATE AD_START_DATE,"+
                          "OP.OF_AD_DATE AD_END_DATE " +
                          "from ABC OP";

        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(dbUrl,dbUser,dbPw);
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sqlSTGOP);
            // System.out.println("Entering while block");
            while (rset.next()) {
                String price = rset.getString("PRICE");
                String reg_price = rset.getString("REGPRICE");
                String prod_id = rset.getString("PROD_ID");
                int quantity_limit = rset.getInt("QUANTITY_LIMIT");
                Date ad_start_date = rset.getDate("AD_START_DATE");
                Date ad_end_date = rset.getDate("AD_END_DATE");
            }
        } catch (SQLException sqe) {
            logger.error("**** SQLException occured while getting Special Price and Quantity Limit:"+sqe);
            error2 = sqe.toString();
            System.out.println("Problem in creating result set" + error2);
        } catch (Exception e) {
            logger.error("**** Exception occured while getting Special Price and Quantity Limit:"+e);
            error2 = e.toString();
            System.out.println("Problem in creating result set" + error2);
        }

The error happened here only. When we restart the process, it goes away. It happens once in a while. When we get the values from resultset, then it throws an invalid rowid sqlexception.

• sqlSTGOP is a string constant refering to above select query.
• driver: oracle.jdbc.driver.OracleDriver
• jdbc:oracle:thin:@a.b.c.d:1521:db
• java version: jdk1.6
• version of jdbc: ojdbc14_g.jar
• dbUrl is the url of database which has this ABC table
• dbPw is the password of that database
• oracle: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

There is no PK or index on this table.

Is someone deleting rows?

There is one process which runs before this process which removes and loads the data under table ABC and when it finishes, then this process runs after that. That is why we are unable to find out, why this error is coming even the earlier process finishes its task before starting of this process.

No other process, procedure, trigger, package is using this table ABC.

Answer 1

go to

http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html

and download

http://download.oracle.com/otn/utilities_drivers/jdbc/11204/ojdbc6.jar
http://download.oracle.com/otn/utilities_drivers/jdbc/11204/orai18n.jar

Remove the class.forname and put the jars above on your classpath. Loose the ojdbc14.

Answer 2

There is no PK ... on this table.

Some might argue that this is not a "proper" table, but that's another "discussion".

This cannot be your actual [Java] code.
Your while loop is iterating through every row returned by the query (of which there could be lots, because your query has no "where" clause). The loop extracts values from each row into local variables. Then the loop ends.
The values in the variables would be from the last row retrieved (which could be any of the rows because your query specifies no "order by" clause) but the loop ends before doing anything useful and the local variables are simply discarded.

ROWID's can change.
If ROW MOVEMENT is enabled on a table, then just updating a row can be enough to change its ROWID. Rebuilding an index on that table is another (more common) way for ROWIDs to change.