无法解决错误-java.sql.SQLException:ORA-01000:超出了最大打开游标 [英] Unable to resolve error - java.sql.SQLException: ORA-01000: maximum open cursors exceeded

问题描述

我做了一个java方法来向数据库添加一行.为了进行测试，我将这种方法称为大约1000次以上.我在准备好的语句上调用了close()方法，并且每当调用此方法以插入行时，我仍然会收到oracle错误.

I made a java method to add a row to a database. I am calling this method about 1000 plus times for testing purposes. I called the close() method on my prepared statement and i am still getting the oracle error whenever this method is called to insert a row.

错误

ORA-01000: maximum open cursors exceeded

源代码

public void insertARow(ArrayList<String> row)
{
    try
    {
        //Proper SQL statement here, checked by running on DB  
        String insert = "INSERT INTO user.info(cola,colb) values(?,?)";

        //Add a row 
        PreparedStatement ps = con.prepareStatement(insert);//con is a connection object 
        //'row' is an arraylist of strings
        for(int i = 0; i < row.size(); i++ )
        {

            int j = 1 +  i ; 
            String temp = row.get(i);
            ps.setString(j , temp);
        }

        ps.executeUpdate();//The reason for problems !!!
        ps.close();

    }catch(SQLException e)
    {
        System.out.println("Cannot add row !");
        e.printStackTrace();
    }
}

推荐答案

如果您要执行1000次相同的操作，我建议re-using相同的PreparedStatement或使用 executeBatch() 组合.

If you're trying to perform the same operation a 1000 times, I would advise re-using the same PreparedStatement or using addBatch() and executeBatch() combo.

如果您打算重复使用PreparedStatement，可以执行以下操作:

public void insertARow(PreparedStatement ps, ArrayList<String> row){
 //your code
}

public void calledMethod(){
 String insert = "INSERT INTO user.info(cola,colb) values(?,?)";
 PreparedStatement ps = null;

 try{
   ps = con.prepareStatement(insert);
   /**
    * Here you make the call to insertARow passing it the preparedstatement that you
    * have created. This in your case will be called multiple times.
    */
   insertARow(ps, row);
 }finally{
   if(ps != null){
     //close ps
   }
 }
}

这篇关于无法解决错误-java.sql.SQLException:ORA-01000:超出了最大打开游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！