Solution to Oracle exceeding the maximum number of open cursors

Time:2020-10-28

This article describes the solution of Oracle exceeding the maximum number of open cursors. For your reference, the details are as follows:

Java code is executing conn.createStatement () and conn.prepareStatement () is actually equivalent to opening a cursor in the database。 In particular, if your createstatement and preparestatement are in a loop, this problem can easily occur. Because the cursor is always open and not closed.

Generally speaking, when we write java code,Both the createstatement and preparestatement should be placed outside the loop, and they should be closed in time after they are used.It is best to close the statement or Preparedstatement immediately after executing an executeQuery, executeupdate, etc., if you do not need to use the data of the resultset.

In the case of open-01000-01000_ Cursors is not a good solution. It’s just a temporary cure, not a permanent cure. In fact, the pitfalls in the code have not been removed. Moreover, in most cases, open_ Cursors only needs to set a small value, which is enough to use, unless there are very special requirements.

If you don’t use connection pooling, there is no problem. Once the connection is closed, the database physical connection will be released and all relevant Java resources can be recycled by GC.

However, if you use connection pool, please pay attention to that connection closing is not physical closing, it is just returning the connection pool. Therefore, Preparedstatement and resultset are both held, and actually occupy the cursor resources of the relevant database. In this case, as long as it runs for a long time, the error “cursor exceeds the maximum value allowed by the database” will be reported, resulting in no program Can access the database normally.

The correct code is as follows:

for(int i=0;i<balancelist.size();i++)
{
  prepstmt = conn.prepareStatement(sql[i]);
  prepstmt.setBigDecimal(1,nb.getRealCost());
  prepstmt.setString(2, adclient_id);
  prepstmt.setString(3, daystr);
  prepstmt.setInt(4, ComStatic.portalId);
  prepstmt.executeUpdate();
  //When the loop reaches a certain number, it must be closed to prevent exceeding the maximum number of cursors
  prepstmt.close();
}

I hope this paper will be helpful to the Oracle database program design.

Recommended Today

php displays Chinese graphically and specifies the ttf font library

1 <?php 2 header("Content-Type:image/png"); 3 $img=imagecreatetruecolor(400,300); 4 // imagejpeg($img); 5 // imagejpeg($img,"./img/copy_img01.jpg",10); 6 $color1=imagecolorallocate($img,100,100,100); 7 $color2=imagecolorallocate($img,255,0,0); 8 $str="北京欢迎你!"; 9 imagefill($img,0,0,$color1); 10 // imagestring($img,5,0,0,$str,$color2); 11 $fontfile="F:\phpStudy\WWW\myweb\FZXBSJW.ttf"; 12 imagettftext($img,24,0,100,100,$color2,$fontfile,$str); 13 imagepng($img); 14 imagedestroy($img); 15 // header("Content-Type:text/html"); 16 // echo "gave over!"; Line 13 imagepng($img) must have, and the Chinese font path must be an absolute path.