ORA-1000エラーはOracle Databaseのセッションでオープンしている
カーソル数が最大値を超えた際に発生するエラーです。
ORA-1000 最大オープン・カーソル数を超えました。
準備
早速やってみるやで彡(゚)(゚) まずはテーブルの準備から。
CREATE TABLE TBL_A(
C1 NUMBER
, C2 VARCHAR2(30)
);
ORA-1000エラーが起きるJavaソース(※アンチパターン)
PreparedStatementのバインド変数なSQLで、
ワザとORA-1000エラーが起きるようにソースを記述しています。
forループ内にprepareStatementメソッドが有って、その度にカーソルが
新規にオープンされて、エラーになってしまうんですやね彡(゚)(゚)
クソコード要素満載なんやけど、ワザとなんでマサカリ飛ばしたら(アカン 彡(-)(-)
import java.sql.*;
import java.util.Date;
public class InsertTest {
public static void main(String[] args) throws Exception {
//DB connection info
final String path = "jdbc:oracle:thin:@127.0.0.1:1521/orcl"; //path
final String id = "xxxxxxxx"; //ID
final String pw = "yyyyyyyy"; //password
int i;
Connection conn = null;
PreparedStatement ps = null;
System.out.println(new Date() + " Connect...");
try {
//DB Connect
conn = DriverManager.getConnection(path, id, pw);
//AutoCommit Setting
conn.setAutoCommit(false);
//Insert Execute
System.out.println(new Date() + " Insert...");
for (i = 1; i <= 2000; i++) {
//Prepared Statement Set.
ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)");
ps.setInt(1, i);
ps.setString(2, "A" + String.valueOf(i));
ps.execute();
}
//Commit
conn.commit();
//Close
conn.close();
} catch(SQLException ex) {
conn.rollback();
ex.printStackTrace();
System.exit(1);
} finally {
if (ps != null) { ps.close(); }
if (conn != null) { conn.close(); }
}
//End
System.out.println(new Date() + " End...");
}
}
実行すると、以下の通りORA-1000エラーが発生するんやで彡(゚)(゚)
$ javac ./InsertTest.java
$ java -classpath .:${ORACLE_HOME}/jdbc/lib/ojdbc8.jar InsertTest
Thu Nov 30 00:57:01 JST 2017 Connect...
Thu Nov 30 00:57:23 JST 2017 Insert...
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:910)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3887)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1079)
at InsertTest.main(InsertTest.java:27)
Caused by: Error : 1000, Position : 0, Sql = INSERT INTO TBL_A (C1, C2) VALUES (:1 , :2 ), OriginalSql = INSERT INTO TBL_A (C1, C2) VALUES (?, ?), Error Msg = ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
... 14 more
$ echo $?
1
回避策1. prepareStatementをforループの外に置く。
prepareStatementをforループの外に置けば、
ORA-1000エラーを回避できます。そりゃそやな?彡(゚)(゚)
:
//Insert Execute
System.out.println(new Date() + " Insert...");
//Prepared Statement Set.
ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)");
for (i = 1; i <= 2000; i++) {
ps.setInt(1, i);
ps.setString(2, "A" + String.valueOf(i));
ps.execute();
}
//Commit
conn.commit();
:
回避策2. オブジェクトのnullチェックをして初回だけprepareStatementを実行する。
オブジェクトのnullチェックをして初回prepareStatementを
実行すれば、ORA-1000エラーを回避できます。
:
//Insert Execute
System.out.println(new Date() + " Insert...");
for (i = 1; i <= 2000; i++) {
//Prepared Statement Set.
if (ps == null) {
ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)");
}
ps.setInt(1, i);
ps.setString(2, "A" + String.valueOf(i));
ps.execute();
}
//Commit
conn.commit();
:
回避策3. try-with-resources構文を採用する。
回避策1.のバリエーションになるですやろうか彡(゚)(゚)
以前の記事で教えて貰ったtry-with-resources構文を採用すると、
初めのアンチパターン的な書き方を「自ずと」回避して、
ORA-1000エラーは発生しません。「自ずと」ってとこがポイントです。
ソースはすっきりするし、良い事ずくめなんやなぁ彡(゚)(゚)
import java.sql.*;
import java.util.Date;
public class InsertTest {
public static void main(String[] args) {
//DB connection info
final String path = "jdbc:oracle:thin:@127.0.0.1:1521/orcl"; //path
final String id = "xxxxxxxx"; //ID
final String pw = "yyyyyyyy"; //password
//try-with-resources Statement
System.out.println(new Date() + " Connect...");
try (
//DB Connect
Connection conn = DriverManager.getConnection(path, id, pw);
//Prepared Statement Set.
PreparedStatement ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)");
) {
//Initialize
int i;
//AutoCommit Setting
conn.setAutoCommit(false);
//Insert Execute
System.out.println(new Date() + " Insert...");
for (i = 1; i <= 2000; i++) {
ps.setInt(1, i);
ps.setString(2, "A" + String.valueOf(i));
ps.execute();
}
//Commit
conn.commit();
} catch(SQLException ex) {
ex.printStackTrace();
System.exit(1);
}
//End
System.out.println(new Date() + " End...");
}
}
回避策4. PreparedStatementオブジェクトを毎回close()する。※2017/11/27追記
ループ内でPreparedStatementオブジェクトを
毎回close()すればORA-1000エラーの発生は回避できます。
:
//Insert Execute
System.out.println(new Date() + " Insert...");
for (i = 1; i <= 2000; i++) {
//Prepared Statement Set.
ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)");
ps.setInt(1, i);
ps.setString(2, "A" + String.valueOf(i));
ps.execute();
ps.close();
}
//Commit
conn.commit();
:
この書き方すると毎回Hard Parseしちゃうかな?と思ったんですが、
文キャッシュの機能で毎回Hard Parseは避けられるもよう彡(゚)(゚)
文キャッシュについて
https://docs.oracle.com/cd/E16338_01/java.112/b56281/stmtcach.htm#i1069942
:
・文の解析と作成の繰返しを回避します。
:
おまけ(※2017/11/30追記)
別の人から「アンチパターンのは変数のスコープがそもそもおかしくねぇか?」って
指摘を受けたので、try句で変数を宣言するようにソースを修正すると……
うーん、自然と回避策1.っぽいコードになっちゃいますやね。
try-with-resources構文と云い、お作法大事ですやね彡(゚)(゚)
import java.sql.*;
import java.util.Date;
public class InsertTest {
public static void main(String[] args) {
//DB connection info
final String path = "jdbc:oracle:thin:@127.0.0.1:1521/orcl"; //path
final String id = "xxxxxxxx"; //ID
final String pw = "yyyyyyyy"; //password
System.out.println(new Date() + " Connect...");
try {
//Init
int i;
//DB Connect
Connection conn = DriverManager.getConnection(path, id, pw);
//AutoCommit Setting
conn.setAutoCommit(false);
//Prepared Statement Set.
PreparedStatement ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)");
//Insert Execute
System.out.println(new Date() + " Insert...");
for (i = 1; i <= 2000; i++) {
ps.setInt(1, i);
ps.setString(2, "A" + String.valueOf(i));
ps.execute();
}
//Commit
conn.commit();
//Close
ps.close();
conn.close();
} catch(SQLException ex) {
ex.printStackTrace();
System.exit(1);
}
//End
System.out.println(new Date() + " End...");
}
}
おまけ2. addBatch(), executeBatch() でSQLを実行(※2019/8/20追記)
コメントで addBatch についてコメント頂いたので、おまけのソースを基に改修してみました。
こんな感じで良いのかしら……彡(゚)(゚) addBatch(バッチ更新) のマニュアルは こちら
import java.sql.*;
import java.util.Date;
public class InsertTest {
public static void main(String[] args) {
//DB connection info
final String path = "jdbc:oracle:thin:@127.0.0.1:1521/orcl"; //path
final String id = "xxxxxxxx"; //ID
final String pw = "yyyyyyyy"; //password
System.out.println(new Date() + " Connect...");
try {
//Init
int i;
//DB Connect
Connection conn = DriverManager.getConnection(path, id, pw);
//AutoCommit Setting
conn.setAutoCommit(false);
//Prepared Statement Set.
PreparedStatement ps = conn.prepareStatement("INSERT INTO TBL_A (C1, C2) VALUES (?, ?)");
//Insert Execute
System.out.println(new Date() + " Insert...");
for (i = 1; i <= 2000; i++) {
ps.setInt(1, i);
ps.setString(2, "A" + String.valueOf(i));
ps.addBatch();
}
//Execute(batch updates)
int[] updateCounts = ps.executeBatch();
System.out.println(new Date() + " Batch Counts..." + updateCounts.length);
//Commit
conn.commit();
//Close
ps.close();
conn.close();
} catch(SQLException ex) {
ex.printStackTrace();
System.exit(1);
}
//End
System.out.println(new Date() + " End...");
}
}
$ javac ./InsertTest.java
$ java -classpath .:/u01/app/oracle/product/version/db_1/jdbc/lib/ojdbc8.jar InsertTest
Tue Aug 20 04:18:11 EDT 2019 Connect...
Tue Aug 20 04:18:13 EDT 2019 Insert...
Tue Aug 20 04:18:14 EDT 2019 Batch Counts...2000
Tue Aug 20 04:18:14 EDT 2019 End...
$