Unable to resolve error - java.sql.SQLException: ORA-01000: maximum open cursors exceeded

Question

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.

Error

ORA-01000: maximum open cursors exceeded

Source Code

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();
    }
}

Where is the connection con created, and where is it closed? It might be helpful to post the surrounding code that uses this method. — Patrick, Aug 28 '12 at 00:30
You've two parameters for your `PreparedStatement` however you're setting them incorrectly as well — Sujay, Aug 28 '12 at 00:32
@veer - is it always good to use only one prepared statement in every code ? — bread butter, Aug 28 '12 at 00:51
your `i` runs from 0 to `row.size() - 1` and you're using `j = 1 + i`, so it would run from 1 to `row.size()`. However you've got two parameters only in your `ps` to set [unless of-course you're sure that row.size() will always be 2...if this is the case, having a loop doesn't make sense] — Sujay, Aug 28 '12 at 00:54
Your code is more than likely throwing an exception in your loop, causing the statement not to be immediately closed. Always use a protecting `finally` block to ensure you release your resources... or, better yet, use the new *`try`-with-resources*. — obataku, Aug 28 '12 at 01:01
Try a catch-all there in stead of only `SQLException` and tell us if there's some other exception in the trace. — obataku, Aug 28 '12 at 01:03
+1 with @veer - especially because of the way you're using `ps.setString(j , temp);` in a loop although you've got only a couple of parameters to set. I am a little skeptic about it! — Sujay, Aug 28 '12 at 01:09

score 0 · Accepted Answer · edited Nov 20 '13 at 20:51

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.

If you're planning to re-use your PreparedStatement, here's something that you can do:

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
   }
 }
}

Unable to resolve error - java.sql.SQLException: ORA-01000: maximum open cursors exceeded

1 Answers1

Linked