ORA-01000: maximum open cursors exceeded - How To Avoid

When you will develop relatively complex form with a big set of View Objects and multiple Master-Detail relationships in the Model, there is a chance during testing you will get ORA-01000: maximum open cursors exceeded exception. It will happen, because there will be too many open row sets, especially in the case of complex Master-Detail relationships. However, its possible to avoid this problem.

Its described in Oracle Fusion Middleware Java API Reference for Oracle ADF Model 11g Release 1 (11.1.1.0.0) for ViewObjectImpl class, setFetchMode(byte) method. Documentation says that there are two modes to fetch data - FETCH_AS_NEEDED and FETCH_ALL. By default, FETCH_AS_NEEDED is set. However, with this setting, if user will scroll through Master, for each Detail group will be opened new row set and not closed (because most probably user will not navigate to the end in the list). This means, many ResultSet's will be created and there will be no available cursors. As documentation recommends, in this case FETCH_ALL should be used.

This setting can be configured declaratively, in View Object wizard. In Tuning section, Retrieve from the Database group, set All at Once instead of As Needed.


JDeveloper automatically will set FETCH_ALL setting in View Object XML:


In order to describe how it works on runtime, let's take an example of Master-Detail form:


In this case, with default FETCH_AS_NEEDED setting, only visible 25 rows will be fetched when Master record with DeartmentId=50 is selected:


If user will not scroll to last record, RecordSet will not be closed.

But, when FETCH_ALL is set, all Detail records for Master record with DepartmentId=50 will be fetched and ResultSet will be closed:


Of course, this can decrease application performance. So, you should use this setting smart.

Download sample application - OpenCursors.zip.

Spanish Summary:

Si se esta desarrollando páginas con acceso a múltiples View Objets, es posible que te topes con problemas asociados a la apertura de muchos cursores en la Base de Datos. Andrejus nos explica en este tópico la manera de evitar este problema, haciendo uso de las personalizaciones a nivel de View Object.