Alex lodeon Alex lodeon - 2 months ago 5
Java Question

The code below can only insert 226 data OUT of the 20000 numbers from attached file into number column of the TEMPTABLE

The code below can only insert 226 out of 20000 numbers from a supplied file into a number column of the TEMPTABLE, and then throws

[ java.sql.SQL.Exception:ORA-00604: error occurred at recursive SQL level
1ORA-01000: maximum open cursors exceeded
ora-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded ]


Here is the code:

private void ButtonloadActionPerformed(java.awt.event.ActionEvent evt) {
PreparedStatement pre2;
//loading the database driver and initiating a connection to it
//all statement in a try and catch block
try {
String driverName="oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);
String url ="jdbc:oracle:thin:@"+serverName+":"+serverport+":"+sid;
conn =DriverManager.getConnection(url,username,password);

// uploading the content on the csv file in the path into the TempTable in the DB
try (BufferedReader br = new BufferedReader(new FileReader(path))) {
String line;
while ((line=br.readLine())!=null) {
String[] value=line.split(",");
String sql1 ="insert into TEMPTABLE(numbers)values('"+(value[0])+"')";
pre2=conn.prepareStatement(sql1);
pre2.executeUpdate();
}
br.close(); // closing the buffered reader
conn.close(); //closing database connection to free system resources
}
}
catch(ClassNotFoundException | SQLException | IOException e) {
JOptionPane.showMessageDialog(null,e);
}


Please can anyone help me fix this?

Answer

You aren't closing your PreparedStatements, and they each use cursor resources on your database.

You could add a pre2.close() after the executeUpdate() - that would fix the immediate issue. But it will be very slow and resource intensive - as noted elsewhere, you should look into batching and bind variables.

Comments