Try to write the source that causes the ORA-1000 error with the bind variable SQL using Java's PreparedStatement and find a workaround.

ORA-1000 error open in oracle database session This error occurs when the number of cursors exceeds the maximum value.

ORA-1000 The maximum number of open cursors has been exceeded.

Preparation

Let's try it immediately. 彡 (゜) (゜) First, prepare the table.

CREATE TABLE TBL_A(
    C1 NUMBER
  , C2 VARCHAR2(30)
);

Java source that causes an ORA-1000 error (* anti-pattern)

PreparedStatement bind variable SQL, The source is written so that tricks and ORA-1000 errors occur.

There is a prepareStatement method in the for loop, and the cursor is moved each time. It will be newly opened and an error will occur. 彡 (ﾟ) (ﾟ)

It's full of shit code elements, but if I skip Masakari with a trick (Akan 彡 (-) (-)

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...");
    }
}

When executed, the following ORA-1000 error occurs. 彡 (ﾟ) (ﾟ)

$ 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

Workaround 1. Put the prepareStatement outside the for loop.

If you put the preparedStatement outside the for loop, You can avoid the ORA-1000 error. Is that so? 彡 (゜) (゜)

：
//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();
：

Workaround 2. Check the object for null and execute prepareStatement only for the first time.

Null check of object and first prepareStatement If you do, you can avoid the ORA-1000 error.

：
//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();
：

Workaround 3. Adopt the try-with-resources syntax.

It will be a variation of workaround 1. 彡 (ﾟ) (ﾟ) If you adopt the try-with-resources syntax that you taught in the previous article, Avoid the first anti-pattern writing "naturally" ORA-1000 error does not occur. The point is "naturally".

The sauce is clean and all the good things are 彡 (ﾟ) (ﾟ)

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...");
    }
}

Workaround 4. Close () the PreparedStatement object every time. * Added 2017/11/27

PreparedStatement object in a loop You can avoid the ORA-1000 error by closing () every time.

：
//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();
：

Is it hard parse every time I write this? I thought, Hard Parse can be avoided every time with the statement cache function. 彡 (ﾟ) (ﾟ)

About statement cache https://docs.oracle.com/cd/E16338_01/java.112/b56281/stmtcach.htm#i1069942 ： -Avoids repeated analysis and creation of sentences. ：

Bonus (* Added 2017/11/30)

Another person asked, "Isn't the scope of variables in anti-patterns strange in the first place?" I was pointed out, so if I modify the source to declare the variable in the try clause ... Well, it naturally becomes a workaround 1.-like code. The try-with-resources syntax is important, isn't it? (゜) (゜)

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...");
    }
}

Bonus 2. Execute SQL with addBatch () and executeBatch () (* Added 2019/8/20)

I received a comment about addBatch in the comments, so I modified it based on the extra source. I wonder if it's okay like this ... 彡 (ﾟ) (ﾟ) The manual for addBatch (batch update) is [here](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...
$