Thread: Oracle cursor will not close

Hi,

I read some data from an Oracle database using a package with a number of functions and refcursors to produce the result sets. The database is read periodicaly via a timer and presented on screen.

The problem is that regardless to if I close the resultset or not, the database cursor ramains and increases for every refresh of the screen. I have tried diffrent tips that i Googled bot nothing works. I hope someone can see if there is something wrong in the code or show me some kind of solution.

public class PanelSQL {
	Tools debug = new Tools();
 
	// DB info
	public JPanel DbInfo(Connection conn) throws SQLException {
 
		// Database info panel
		JPanel panel = getPanel(" Database ");
		panel.setLayout(new MigLayout("wrap 4","[] 16 []"));
 
		// Prepare statement
		String query = "begin ? := cmt.db_info; end;";
		ResultSet rs = prepSql(conn, query);
 
		try {
			rs.next();
			panel.add(new JLabel("Database/instance name:"),"left");
			panel.add(new JTextField(rs.getString(1)+"/"+rs.getString(2)),"grow");
			panel.add(new JLabel("Version:"),"left");
			panel.add(new JTextField(rs.getString(3)),"grow");
			panel.add(new JLabel("Database role:"),"left");
			panel.add(new JTextField(rs.getString(4)),"grow");
			panel.add(new JLabel("Instance role:"),"left");
			panel.add(new JTextField(rs.getString(5)),"grow");
			panel.add(new JLabel("Open mode:"),"left");
			panel.add(new JTextField(rs.getString(6)),"grow");
			panel.add(new JLabel("Log mode:"),"left");
			panel.add(new JTextField(rs.getString(7)),"grow");
			panel.add(new JLabel("Status:"),"left");
			panel.add(new JTextField(rs.getString(8)),"grow");
			panel.add(new JLabel("Database logins:"),"left");
			panel.add(new JTextField(rs.getString(9)),"grow");
			panel.add(new JLabel("Startup time:"),"left");
			panel.add(new JTextField(rs.getString(10)),"grow");
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try { rs.close(); 
			} catch (Exception f) {
				f.printStackTrace();
			}
		}
 
		return panel;
	}

Cheers,
Magnus

Since I'm a newbie at Java I'm not shure. I read somewhere that it can make a difference to close the statement first and then the resultset. I tried to use something like...

conn (connection passed in to method as parameter)
PreparedStatement stmt = null;
ResultSet rs = null;
try {
    stmt = conn.prepareStatement(// Some SQL //);
    rs = stmt.executeQuery();
} catch {
    // Some error
} finally {
    try { if (rs != null) rs.close(); } catch (Exception e) {};
    try { if (stmt != null) stmt.close(); } catch (Exception e) {};
}

... but I didn't get it to work. I couldn't find out how to call the Oracle package with "begin ? := cmt.db_info; end;" that way. I got an error like 'missing parameter' or similar, so I ditched that for the time beeing.

The connection is established at startup (simple login to the db) and then I pass the connection between the classes and methods. It feels a bit awkward to do it that way, but it was the only way I could think of. I refresh the window every 5 sec so I believe I can't close the connection after every refresh.

Originally Posted by aussiemcgr

Well I'm only asking for debugging reasons, not as a suggestion for changing your design. For example, if the cursor goes away when the connection is closed, that would be good news. If the cursor stays, that could indicate that removing the cursors might be out of our control (at least on the face of it, there might be a way, but it would probably be complex).

Another thing you could try: if you perform a garbage collection (System.gc()) after closing the ResultSet and waiting a few seconds before checking the cursor, does the cursor go away? Perhaps closing the result set does not explicitly remove the cursor, and it is held on to until java says otherwise (which would most likely occur during garbage collection).

The cursors do close when I close the connection.

I tried to perform an explicit garbage collection but with no result.

--- Update ---

Originally Posted by copeg

How do you know it's an issue with the database cursor? Have you printed out the row number for every time you call next()?

I monitor the open cursors in the database and they increase every time I do a 'prepSql(conn, query)' which is the expected behaviour. It all ends with a sql exception (ORA-01000 maximum open cursors exceeded). It's quite obvious where the problem lies since I fetch 8 resultsets for every refresh whitch adds 8 cursors in the database, but they will not release.

Hi,

Thank you all. The problem was mainly some seriously damaged braincells on my side, and and a few bugs in the code. It works now, case is solved (the code anyways) and I will be back with an explanation later.

//Cheers