ORA-00604: error occurred at recursive SQL level 1

Question

I started getting the below SQL exception and I don't know what's the root cause for this exception? I am also closing dbconnection and prepared statement too. Then what's the problem?

java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded

Below is my code which I am using. Anything wrong in my code?

for (Entry<Integer, LinkedHashMap<Integer, String>> entry : GUID_ID_MAPPING.entrySet()) {

    pstatement = db_connection.prepareStatement(PDSLnPConstants.UPSERT_SQL); // create a statement
    pstatement.setInt(1, entry.getKey());
    pstatement.setString(2, entry.getValue().get(PDSLnPConstants.CGUID_ID));
    pstatement.setString(3, entry.getValue().get(PDSLnPConstants.PGUID_ID));
    pstatement.setString(4, entry.getValue().get(PDSLnPConstants.SGUID_ID));
    pstatement.setString(5, entry.getValue().get(PDSLnPConstants.UID_ID));
    pstatement.setString(6, entry.getValue().get(PDSLnPConstants.ULOC_ID));
    pstatement.setString(7, entry.getValue().get(PDSLnPConstants.SLOC_ID));
    pstatement.setString(8, entry.getValue().get(PDSLnPConstants.PLOC_ID));
    pstatement.setString(9, entry.getValue().get(PDSLnPConstants.ALOC_ID));
    pstatement.setString(10, entry.getValue().get(PDSLnPConstants.SITE_ID));
    pstatement.executeUpdate();

    }

} catch (SQLException e) {
    getLogger().log(LogLevel.ERROR, e);
} finally {
    if (pstatement!= null) {
    try {
        pstatement.close();
        pstatement = null;
    } catch (SQLException e) {
        getLogger().log(LogLevel.ERROR, e.getMessage(), e.fillInStackTrace());
    }
    }
    if (db_connection!= null) {
    try {
        db_connection.close();
        db_connection = null;
    } catch (SQLException e) {
        getLogger().log(LogLevel.ERROR, e.getMessage(), e.fillInStackTrace());
    }
    }

Glenn · Accepted Answer · 2012-08-23 02:24:19Z

I think the PreparedStatement definition should be pulled out of the loop and reused within the loop by calling clearParameters:

pstatement = db_connection.prepareStatement(PDSLnPConstants.UPSERT_SQL); // create a statement

for (Entry<Integer, LinkedHashMap<Integer, String>> entry : GUID_ID_MAPPING.entrySet()) {

    pstatement.setInt(1, entry.getKey());
    pstatement.setString(2, entry.getValue().get(PDSLnPConstants.CGUID_ID));
    pstatement.setString(3, entry.getValue().get(PDSLnPConstants.PGUID_ID));
    pstatement.setString(4, entry.getValue().get(PDSLnPConstants.SGUID_ID));
    pstatement.setString(5, entry.getValue().get(PDSLnPConstants.UID_ID));
    pstatement.setString(6, entry.getValue().get(PDSLnPConstants.ULOC_ID));
    pstatement.setString(7, entry.getValue().get(PDSLnPConstants.SLOC_ID));
    pstatement.setString(8, entry.getValue().get(PDSLnPConstants.PLOC_ID));
    pstatement.setString(9, entry.getValue().get(PDSLnPConstants.ALOC_ID));
    pstatement.setString(10, entry.getValue().get(PDSLnPConstants.SITE_ID));
    pstatement.executeUpdate();

    pstatement.clearParameters();

}

You may also want to investigate batch processing (addBatch). If you are testing, you may need to wait a bit for the existing "open" cursors to be cleaned up.

Tadiwanashe · Accepted Answer · 2013-07-08 15:12:53Z

2

Well I guess I am giving you one way I went around it but anyway a lot of people are going to come across such a problem so I might as well share.

I was using NetBeans 7.3.1 and referring to an Oracle Database on a remote Server so I couldn't edit the Registry Keys and instead I realised I was using ojdbc14.jar and all i needed was ojdbc6.jar in my classpath.

Solution: Used ojdbc6.jar instead of ojdbc14.jar

answered Jul 8, 2013 at 15:12

Tadiwanashe

1,31214 silver badges15 bronze badges

Collectives™ on Stack Overflow

ORA-00604: error occurred at recursive SQL level 1

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related