ORA-01000: Open Cursors in Oracle

During some development at the office, we encountered some problems with Oracle having too many open cursors. We kept getting ORA-01000 error at random times.

What is a cursor

Very simple explained, a cursor in Oracle is a pointer to your result. The more complex explanation is that it is a pointer/handler into the context area where it refers to a chunk of memory that has been allocated. Two types of cursors exist: Implicit cursors and Explicit cursors. For more info on cursors, ask google :)

Problem

At work we have some Java Servlets querying an Oracle database at one day we started getting this error:

ORA-01000: maximum open cursors exceeded

This would block any new connections to Oracle, but over time we would be able to reconnect. A restart of the Servlet Runner (in this case Tomcat) would also enable us to connect again.

The problem arises when too many cursors are open at one time to Oracle. In Oracle it can be defined how many cursors are allowed, but exceeding the normal amount usually indicates that there is another problem. The usual culprit is that the code does not properly close Statements and therefore the cursor remains open until it times out or the program terminates.

Solution to ORA-01000

To start with, I will present a piece of code that will create open cursors:

public static Vector<Integer> incorrect()
{
	Vector<Integer> result = new Vector<Integer>();
	Statement stmt = null;
	ResultSet rs = null;
	try
	{
		stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT myInt FROM SomeTable");
		while(rs.next())
		{
			result.add(rs.getInt("myInt"));
		}
	}
	catch(SQLException sqle)
	{
		sqle.printStackTrace();
	}
	return result;
}

As it can be seen in the code, the stmt object is never closed. One correct way of writing this method would be:

public static Vector<Integer> correct()
{
	Vector<Integer> result = new Vector<Integer>();
	Statement stmt = null;
	ResultSet rs = null;
	try
	{
		stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT myInt FROM SomeTable");
		while(rs.next())
		{
			result.add(rs.getInt("myInt"));
		}
	}
	catch(SQLException sqle)
	{
		sqle.printStackTrace();
	}
	finally
	{
		try
		{
			if(rs != null)
			{
				rs.close();
			}
			if(stmt != null)
			{
				stmt.close();
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
	}
	return result;
}

The try-catch statement was changed to a try-catch-finally that secures the stmt object to be closed.

To detect Open Cursors before it becomes a problem,  run the following query in Oracle:

SELECT user_name, sql_text FROM v$open_cursor ORDER BY user_name, sql_text;

This will display the last run SQL in each open cursor – but be aware lots of internal Oracle users will also be shown, do not get spooked by that :-)

Messing around with Microsoft XNA

The last couple of days I’ve been messing around with XNA 4.0. I have never tried it before – but a lot of things are real easy to do, but one thing baffled me – how hard is was to draw a line.

After searching a bit on the topic, I decided to make a method for drawing lines – just thought I would share it.

private void DrawLine(SpriteBatch batch, float width, Color color, Vector2 p1, Vector2 p2)
{
	Texture2D blank = new Texture2D(GraphicsDevice, 1, 1, false, SurfaceFormat.Color);
	blank.SetData(new[] { Color.White });
	float angle = (float)Math.Atan2(p2.Y - p1.Y, p2.X - p1.X);
	float length = Vector2.Distance(p1, p2);
	Vector2 scale = new Vector2(length, width);
	batch.Draw(blank, p1, null, color, angle, Vector2.Zero, scale, SpriteEffects.None, 0);
}

Can it really be true that this is the smartest way to draw a line?