Executing stored procedure with java and ORA-01000 exception.

When you are dealing with database stored procedures and Java EE applications you can choose from many options.
One of them is spring and it’s support for a JDBC within JdbcTemplate class. http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html

In this post, I want to focus on one method from this class and problem with opened cursors: ORA-01000: maximum open cursors exceeded oracle exception.

When you are using ResultSet class database cursor needs to be opened and points to the first position of the data which query or stored procedure return. But who should be responsible for closing it?

Javadoc points:
A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed or used to retrieve the next result from a sequence of multiple results.

but in my case I was using JDBCTemplate class from spring and I thought that it can handle all boilerplate code for me and I don’t need to manually close connection and statement. I used in my project method:

public  T execute(CallableStatementCreator csc, CallableStatementCallback action)

to execute stored procedure which as a one element returns cursor from which I can read the data through ResultSet. Code looks like this:

jdbcTemplate.execute(
    cs,//this is callable statement
    new CallableStatementCallback<Long>() {
        public Long doInCallableStatement(CallableStatement cs) throws SQLException {
            Long count = null;
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject("out_cursor");
            if (rs.next()) {
                count = Long.valueOf(rs.getString("COUNT(1)"));
            }
            return count;
        }
    });

I thought that it’s enough and I don’t need to do more – spring should handle everything else for me. I’ve checked the source code of the execute method and it contains

JdbcUtils.closeStatement(cs);
DataSourceUtils.releaseConnection(con, getDataSource());

The first line closes Statement, in my case it’s CallableStatement and second returns Connection to a connection pool.
The problem is that those lines don’t close ResultSet and after performance tests I ended with ORA-01000: maximum open cursors exceeded oracle exception.

If you want to check open cursors within database you can use below queries:

--total cursors open, by username & machine
SELECT SUM(a.value) total_cur
      ,AVG(a.value) avg_cur
      ,MAX(a.value) max_cur
      ,s.username
      ,s.machine
  FROM v$sesstat  a
      ,v$statname b
      ,v$session  s
 WHERE a.statistic# = b.statistic#
   AND s.sid = a.sid
   AND b.name = 'opened cursors current'
 GROUP BY s.username
         ,s.machine
 ORDER BY 1 DESC;

 --max open cursors per session
 SELECT * FROM v$parameter WHERE NAME = 'open_cursors'

The simplest solution for me is to close ResultSet manually with a close method
rs.close(); or you can use a method from spring JdbcUtils closeResultSet.

If you are really curious want to see how many connections are established and active between database and your connection pool you can use below query:

select s.program, s.server, p.spid server_pid, s.username
from v$session s, v$process p
where s.type = 'USER'
and p.addr(+) = s.paddr;