Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
3
Help us understand the problem. What is going on with this article?
@ora_gonsuke777

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

More than 1 year has passed since last update.

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...
$
3
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ora_gonsuke777
やきうのお兄ちゃんなITエンジニア、主にOracle Database廻りを担当。日本オラクル株式会社 柴田 歩 ※本ブログに記述された見解は私個人の見解であり、所属する会社&組織の見解を必ずしも反映したものではありません。ご了承ください。
oracle
Oracle Cloudは、最先端の機能をSoftware as a Service、Platform as a ServiceおよびInfrastructure as a ServiceおよびData as a Serviceとして提供します。

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
3
Help us understand the problem. What is going on with this article?