Note: we reuse single connection.
************************************************
public Connection connection() {
try {
if ((connection == null) || (connection.isClosed()))
{
if (connection!=null)
log.severe("Connection was closed !");
connection = DriverManager.getConnection(jdbcURL, username, password);
}
} catch (SQLException e) {
log.severe("can't connect: " + e.getMessage());
}
return connection;
}
**************************************************
public IngisObject[] select(String query, String idColumnName, String[] columns) {
Connection con = connection();
Vector<IngisObject> objects = new Vector<IngisObject>();
try {
Statement stmt = con.createStatement();
String sql = query;
ResultSet rs =stmt.executeQuery(sql);//oracle increases cursors count here
while(rs.next()) {
IngisObject o = new IngisObject("New Result");
o.setIdColumnName(idColumnName);
o.setDatabase(this);
for(String column: columns)
o.attrs().put(column, rs.getObject(column));
objects.add(o);
}
rs.close();// oracle don't decrease cursor count here, while it's expected
stmt.close();
}
catch (SQLException ex) {
System.out.println(query);
ex.printStackTrace();
}
you close cursors AFTER you use them. Hence you close a ref cursor after you fetch from it. If you lose your result set -- that indicates you are not done with the ref cursor. just treat a ref cursor as you would any other cursor, close it after you are finished with it.
Yes, closing/killing a session will close any related cursors.
Closing a cursor instructs Oracle to release allocated memory at an appropriate time. If you declare a cursor in an anonymous block, procedure, or function, the cursor will automatically be closed when the execution of these objects end. However, you must explicitly close package-based cursors.
If you open such a cursor, it will stay open until you CLOSE it explicitly or you disconnect your Oracle session.
The init.ora parameter open_cursors
defines the maximum of opened cursors a session can have at once. It has a default value of 50. If the application exceeds this number the error "ORA-01000: maximum open cursors exceeded" is raised.
Therefore it's mandatory to close the JDBC resources when they are not needed any longer, in particular java.sql.ResultSet and java.sql.Statement. If they are not closed, the application has a resource leak.
In case of reusing the Connection object, you must be aware of the fact that the opened oracle cursors are kept open and in use as long the connection exists and the transaction has not ended. When the application commits, the opened cursors are released.
Therefore as an application designer you need to know a rough estimation of the needed open cursors for your most complex transaction.
The difficulty lies in the inability of oracle's internal parameter views (v$open_cursor, v$sesstat, et. al.) to show the difference between opened cursors, which are reusable and opened cursors, which are still blocked (not reusable!) by an unclosed ResulSet or Statement. If you close all Statement and ResultSet objects in your finally block, your application is perfectly fine.
Adjusting the init.ora parameter works like this (our application needs 800 cursors at a maximum)
ALTER SYSTEM SET open_cursors = 800 SCOPE=BOTH;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With