To overcome "Maximum open cursors exceeded" issue

When we execute a query in oracle, a ResultSet is created and stored in the memory. Oracle allows the programmer to access this ResultSet in the memory through cursors.

When we fire SELECT query / DML query from Java code, Oracle returns the reference of this created cursor to the Java program, which we refer as a ResultSet. Following is the hierarchy of creating objects in JDBC:

[For DML queries, we don't require "ResultSet" object]

Now, when we execute this query in loop for multiple records, we can use the same "Connection" object, but we'll have to close ResultSet object and Statement object at the end of each loop.

[Here we're using distributed database connection which means for each request, we create new "Connection" object and after the request is processed, we close this "Connection" object. Now, there might be the case that for one request, we need to fire multiple SELECT / DML queries, so we use the same "Connection" object to process this request.]

Closing ResultSet object and Statement object at the end of each loop will make sure that the cursors opened by executing this query will be closed / released.

[For DML queries, we need to close only "Statement" object].

Finally we close the connection object when we finish executing the queries in loop.

All the resources (including cursors) are released when the "Connection" object is closed.

Following this, we'll never encounter "Maximum open cursors exceeded" issue in any application :)