ORA-00604: error occurred at recursive SQL level 1

oracle, prepared-statement, sql, sqlexception

Solution

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.

Problem

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()); } } ```

Original source