先日出くわした「ORA-01000:最大オープン・カーソル数を超えました」というOracleDBのエラーについてメモ。

問題となったのは下記のようなコード
(業務コードを晒す訳には行かないため、サンプルとして書き直したもの。動作未確認。)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class cursorLeakeSample {
	public cursorLeakeSample(List idList) {
		try {
			// Oracle JDBC Driverのロード
			Class.forName("oracle.jdbc.driver.OracleDriver");

			// コネクション取得
			Connection connection = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");

			String sql = "SELECT NAME FROM ITEM WHERE ID = ?";
			PreparedStatement statement = null;

			try {
				for (String id : idList) {

					Object[] params = new Object[] { id };

					// SQLにパラメータ埋め込みと実行
					statement = connection.prepareStatement(sql);
					statement.setObject(1, params[0]);
					statement.executeUpdate();

					connection.commit();
				}
			} catch (SQLException e) {
				// SQL実行失敗
				connection.rollback();
				e.printStackTrace();
			} finally {
				statement.close();
				connection.close();
			}
		} catch (SQLException e) {
			// コネクションの接続・切断・ロールバック、ステートメントの解放に失敗
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// Oracle JDBC ドライバが見つからなかった
			e.printStackTrace();
		}
	}
}

強調してあるところが原因となった部分です。
なぜいけないのかわかりますか？
まず、変数statementを空箱として作成して、ループ内で取得した値を入れて使い回し、最後に解放。
一見、解放漏れを起こしてなさそうに見えるのですが、すこし処理を追うと問題を見つけることができます。

“prepareStatement”はSQLの事前準備のため暗黙でカーソルを作成します。
このことを知っていると問題のコードではループ内でたくさんの暗黙カーソルが生成されていることになります。
そして、解放されているのはループの外に抜けたときに格納されていた最後の”prepareStatement”の1つだけ。

たとえば、20件処理をしたなら19件は解放できていないというわけです。

では、どう書けばいいのか

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class cursorLeakeSample {
	public cursorLeakeSample(List idList) {
		try {
			// Oracle JDBC Driverのロード
			Class.forName("oracle.jdbc.driver.OracleDriver");

			// コネクション取得
			Connection connection = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");

			String sql = "SELECT NAME FROM ITEM WHERE ID = ?";
			PreparedStatement statement = connection.prepareStatement(sql);

			try {
				for (String id : idList) {

					Object[] params = new Object[] { id };

					// SQLにパラメータ埋め込みと実行
					statement.setObject(1, params[0]);
					statement.executeUpdate();

					connection.commit();
				}
			} catch (SQLException e) {
				// SQL実行失敗
				connection.rollback();
				e.printStackTrace();
			} finally {
				statement.close();
				connection.close();
			}
		} catch (SQLException e) {
			// コネクションの接続・切断・ロールバック、ステートメントの解放に失敗
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// Oracle JDBC ドライバが見つからなかった
			e.printStackTrace();
		}
	}
}

たぶんこう。”PreparedStatement”を使用するなら。
ただの”Statement”を利用するならfor文内でclose();すればいいかと思います。