Oracle open cursor problems

318 Views Asked by At

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?

0

There are 0 best solutions below