JavaのPreparedStatementを使ったバインド変数なSQLで、ワザとORA-1000エラーを起こすソースを書いてみて回避策を探る。
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(バッチ更新) のマニュアルはこちら⇒ https://docs.oracle.com/cd/F19136_01/jjdbc/performance-extensions.html#GUID-FEECA64F-44F4-453F-B8A8-AFBF6D29ABA4
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... $