Getting java.sql.SQLException: - ORA-01000: maximum open cursors exceeded for DML (Update Statement)

Question

I am reading an excel file that contains 30000 rows and trying to update an Oracle dB table field based on some logic. My Java application error out "java.sql.SQLException: - ORA-01000: maximum open cursors exceeded" when it writes approximately 700th record in the table. Need help in optimising the code so as to avoid this error.

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.ResourceBundle;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import oracle.jdbc.driver.OracleDriver;

public class UpdateTest {
    private static Connection conn = null;
    static ResourceBundle bundle = ResourceBundle.getBundle("PropertiesFile");
    public static void main(String[] args) {

    String filename = bundle.getString("FILEPATH") + bundle.getString("FILENAME");
    FileInputStream fileInputStream = null;
    String input = null;
    PreparedStatement preparedStatement = null;
    Integer result = null;
    int counter = 0;

    try {
        DriverManager.registerDriver(new OracleDriver());
        conn = DriverManager.getConnection(
                bundle.getString("DATABASE_URL"), 
                bundle.getString("DATABASE_USERNAME"),
                bundle.getString("DATABASE_PASSWORD"));
        conn.setAutoCommit(false);

        fileInputStream = new FileInputStream(filename);
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        System.out.println("Number of records to be updated: " + (sheet.getPhysicalNumberOfRows() - 1));

        Iterator i = sheet.iterator();
        while (i.hasNext()) {
            XSSFRow row = (XSSFRow) i.next();
            input = row.getCell(0).toString();
            preparedStatement = conn.prepareStatement("update table1 set column1='value' where input=?");
            preparedStatement.setString(1, input);
            result = preparedStatement.executeUpdate();
        }

        if (preparedStatement != null) {
            preparedStatement.close();
        }
        conn.commit();
        conn.close();

    } catch (Exception e) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        e.printStackTrace();
    } finally {
        try {
            if (conn != null && !conn.isClosed()) {
                if (!conn.getAutoCommit()) {
                    conn.commit();
                    conn.setAutoCommit(true);
                }
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

}

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2018-04-20T10:48:58

Each call to prepareStatement() creates a new cursor in the Oracle server which is not closed in your loop.

The correct solution to avoid the "too many open cursors" is to only create one cursor by preparing the statement only once before the loop.

preparedStatement = conn.prepareStatement("update table1 set column1='value' where input=?");

Iterator i = sheet.iterator();
while (i.hasNext()) {
    XSSFRow row = (XSSFRow) i.next();
    input = row.getCell(0).toString();
    preparedStatement.setString(1, input);
    result = preparedStatement.executeUpdate();
 }

Then close it after the loop in a finally block.

Calling prepareStatement() in a loop defeats the purpose and intention of a PreparedStatement.

Getting java.sql.SQLException: - ORA-01000: maximum open cursors exceeded for DML (Update Statement)

2 Answers