ORA-01000 maximum open cursors exceeded

" java.sql.SQLException : ORA-01000: maximum open cursors exceeded "

-- ORA-01000

I was recently asked my a colleague to help in resolving the ORA-01000 error. At a first glance, everything looked fine in the Java Code & it was baffling - it took sometime for enlightenment to illuminate the nature of the problem and one possible solution.

The program inherited by my colleague had two Java classes - Class A and Class B. Class A calls a method named insert() in Class B to save data into the database. Class A passed an ArrayList of Java Beans to the Class B's insert() method.

The logic to save the data was a plain - vanilla for loop :-

for ( int i = 0 ; i < number ; i++)
{

psSaveData = conOraleConnection.prepareStatement(insertSQL);
..........
..........
psSaveData.setString(1,dataBean.getId());
psSaveData.setString(2,dataBean.getName());
psSaveData.setString(3,dataBean.getAge());
..........
..........
psSaveData.executeUpdate();
}

Now, this piece of code was failing after the first 1000 rows !

We took sometime to methodically analyze the problem using an approach outlined at this link from BEA's Website. However, the problem persisted - either we missed something obvious or we were looking at something new. And then it occured to us :-

psSaveData = conOraleConnection.prepareStatement(insertSQL);

We finally figured out that the problem was caused by the statement marked in red. The line was actually telling the Oracle Database to precompile the same statement 1000 time !!

How does this matter ?

The Oracle Database contains a SQL Engine to deal with all SQL statements. The SQL Engine generally follows a two step process to execute a SQL Statement:-

• parse : to check for syntax and semantic correctness.
• execution plan : an optimized plan that details the instructions to execute the SQL statement.

The SQL Statement and the corresponding execution plan are stored together in a structure called a "Cursor". The Cursor is then stored in a special area of database memory called the "Shared Pool".

You can get handle to a cursor from a variety of languages like PL/SQL, Java, etc. You can then :-

• Open the cursor.
• Fetch the results
• Close the cursor.

The Oracle database opens an "implicit cursor" for every SQL Statement that it encounters. Hence, a cursor is opened for every SELECT, INSERT, etc. statement that is issued to the SQL Engine.

Hence, you only need to prepare the "blueprint" once and execute it multiple times. The piece of code marked in red was preparing the "blueprint" 1000 times - as a result, 1000 cursors were opened !

We simply moved this line out of the for loop and solved one bottleneck.

We also noticed that the plain-vanilla JDBC Insert statement was not suitable for batch inserts. Well, that's another story :)