ora-0100 maximum number of cursors exceeds

I am trying to insert more than 1 lac records, and it is inserting successfully. but the below exception is displaying...

ora-0100 maximum number of cursors exceeds

wht might be the reason for this and how to solve this.

You are opening more connections than Oracle is configured to allow. Do you always close connections when you are finished with them?

Actually i am using one connection, and im adding all statements to batch ( statement.addBatch(); ).
For every 1000 statements im executing batach. If something happens wrong thn it should rollback everything.

Can we see your code?

statement5=connection.prepareStatement("insert into TRANSDETAIL(TRANSNO,ITEMCODE,SERIALNUMBER,MOBILENUMBER,INVOICENO) values(?,?,?,?,?)");
                statement6=connection.prepareStatement("insert into CHANNELINVENTORY(ITEMCODE,SERIALNUMBER,MOBILENUMBER,CHANNELCODE,CURRENTSTATUS,LASTUPDATEON,CIRCLEID) values(?,?,?,?,?,sysdate,?)");
                for(int i=0;i<primarySuccess.size();i++){
                PrimaryUploadForm primaryForm=new PrimaryUploadForm();
                 primaryForm=(PrimaryUploadForm)primarySuccess.get(i);
                if(SIM.equalsIgnoreCase(itemCategory)){

statement5.setInt(1,maxTrans);
                   statement5.setString(2,primaryForm.getItemCode());
                   statement5.setString(3,primaryForm.getSerialNumber());
                   statement5.setString(4,primaryForm.getMsisdn());
                   statement5.setString(5,primaryForm.getInvoiceNumber());
                   //statement5.executeUpdate();
                   if (i > 0 && i % 1000 == 0){
                        statement5.executeBatch();
                         statement5.addBatch();
                         statement5.clearParameters();
                    }else{
                  statement5.addBatch();
                  statement5.clearParameters();
                    }
                  // statement5.addBatch();
                   statement6.setString(1,primaryForm.getItemCode());
                   statement6.setString(2,primaryForm.getSerialNumber());
                   statement6.setString(3,primaryForm.getMsisdn());
                   statement6.setString(4,primaryForm.getDistributorCode());
                    statement6.setString(5,"UN");
                    statement6.setString(6,circleId);
                   //statement6.executeUpdate();
                    if (i > 0 && i % 1000 == 0){
                      statement6.addBatch();
                      statement6.clearParameters();
                        statement6.executeBatch();
                    }else{
                    statement6.addBatch();
                    statement6.clearParameters();
                    }
                   // statement6.addBatch();
                }

statement5.executeBatch();
                statement6.executeBatch();
        connection.commit();
      
      }  }catch (Exception ex) {
       
           try {
                connection.rollback();
            } catch (SQLException ex1) {
               System.out.println("From insertPrimaryUploadDetails the While rollback  Exception is---->"+ex1.getMessage());
                
            }

}finally{
            if(connection!=null){
                try {
                    //statement.close();
                    System.out.println("From finally block------------");
                    connection.close();
                } catch (SQLException ex) {
                    System.out.println("From insertUserDetails the Exception is---->"+ex.getMessage());
                }
            }
        }

close all your preparedStatement at the end of loop.

Balu Sadhasivam wrote:
close all your preparedStatement at the end of loop.

Thanks for you reply,

Actually i had closed the connection in the finally block , is it required to closed the preparedstatement saparately??

Actually i had closed the connection in the finally block , is it required to closed the preparedstatement saparately??

Since you create statements inside loops , close at end of loop.. in all cases , check for null and close in finally block.

thanks for your reply,

i will check for null and close it in the finally block.