1

I have an probolem with my java application. I am getting ORA-01000 maximum cursor opened error altough I am closing all connection statements and cursors such as:

String proc = "{call sp_name(?)}";
Connection connection = getConnection();

if(connection == null)
    throw new SQLException("Null connection");

CallableStatement procin = connection.prepareCall(proc);
if(procin == null)
   throw new SQLException("Null statement");

procin.setInt(1,customerId);
procin.execute();

if(procin != null)
   procin.close();
if(connection != null)
   connection.close();

When I query for the max limit and open cursor, I get limit 400, open cursor 300.

select 
   max(a.value) as hwm_open_cur, 
   p.value      as max_open_cur
from 
   v$sesstat a, 
   v$statname b, 
   v$parameter p
where 
   a.statistic# = b.statistic# 
and 
   b.name = 'opened cursors current'
and 
   p.name= 'open_cursors'
group by p.value;

In other case when I query open cursors related to me I get only 11 open cursors and therefore I do not understand how can I cause this.

SELECT LAST_SQL_ACTIVE_TIME  ,SQL_TEXT 
FROM  v$open_cursor WHERE UPPER(SQL_TEXT) LIKE '%COLL%' order by sql_text

So here is the question how can I get this problem and is something is wrong with the queries above?

user1474111
  • 1,356
  • 3
  • 23
  • 47
  • Have you read through this? http://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded. Do you have visibility into what occurs inside sp_name? – Mark Leiber Apr 16 '15 at 12:36
  • Is this the exact code you are running? If any error occurred during execution you would potentially leave open connections since your close statements are not part of a finally block. – Doug Porter Apr 16 '15 at 19:17
  • yes I am aware of it but I did not get any exception during execution therefore I have not focused on that part yet. – user1474111 Apr 17 '15 at 08:21

0 Answers0