ねら～ITエンジニア雑記

やきうのお兄ちゃんが綴るOracle Databaseメインのブログ

JavaのPreparedStatementを使ったバインド変数なSQLで、ワザとORA-1000エラーを起こすソースを書いてみて回避策を探る。

Oracle JDBC Java Database

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...
$