How many opened cursors?

See Monitoring, Tuning(to tune session_cached_cursors)

How to close a cursor in Java jdbc?
    
String q = "select 'pippo' from dual";
Statement st = c.createStatement();
ResultSet rs = st.executeQuery(q); 
rs.next();
System.out.println(rs.getString(1));
rs.close();
/* 
 * no matter if you close the statement before the result set or viceversa
 * anyway documentation says you should close first the result set
 */
rs.getStatement().close();

!!Caution!! 
I believe a lot of confusion about open cursors vs. cached cursors comes 
from the names of the Oracle dynamic performance views used to monitor them. 
v$open_cursor shows cached cursors, not currently open cursors, by session. If 
you're wondering how many cursors a session has open, don't look in v$open_cursor. It shows the 
cursors in the session cursor cache for each session, not cursors that are actually open.

Number of opened cursors
select s.username, a.sid, a.value
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'
--and s.sid = (select sid from v$mystat where rownum = 1)

See details for current session using:

select * from (
	select v$sqltext.sql_text, v$sqltext.hash_value, s.sid, count(*) counta
	from v$sqltext, v$session s, v$open_cursor
	where v$sqltext.hash_value = v$open_cursor.hash_value 
	and v$open_cursor.sid = s.sid
	and piece = 0
	--and s.sid = 185
	--current user 
	and s.sid = (select sid from v$mystat where rownum = 1)
	group by v$sqltext.sql_text, v$sqltext.hash_value, s.sid
) order by counta desc

Hey!! I don't see anything in v$open_cursor but my application receives ORA-1000
In such case the application is submitting wrong queries without closing cursor. 
The only solution is to trace the application and look in trace for 'PARSE ERROR' to identify wrong queries
You don't believe it? Try this java:

  public static void main(String args[]) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection c = DriverManager.getConnection(
    "jdbc:oracle:thin:@" + "localhost" + ":" + 1521 + ":" + "al12", 
      "elettro", "mypwd");
    int yourMaxOpenCursors = 100;
    for(int i=0; ; i++) {
      /* lets quickly reach max open cursors state */
      try {
        PreparedStatement p = c.prepareStatement("uiuuhihb");
        p.execute();
      } catch(SQLException ex) {  
        /* I don't close the cursor */
        //p.close();
        ex.printStackTrace();
      }
      /* calm down when ORA-01000 appears */
      if(i>yourMaxOpenCursors)
        Thread.sleep(5000);
    }
  }

Tune the session_cached_cursor parameter
#This is my preferred query:
select a.sid, a.username, a.tot_parse_count, a.session_cursor_cache_hits, 
  session_cursor_cache_count, currently_opened,
  decode(a.tot_parse_count, 0, 0, a.session_cursor_cache_hits*100/a.tot_parse_count) "%",
  osuser, machine, terminal, program, module, action, event, state from (
  select s.sid, s.username, 
  (select value from V$sesstat a, v$statname b where a.statistic# = b.statistic# 
   and s.sid = a.sid and b.name = 'parse count (total)') tot_parse_count,
  (select value from V$sesstat a, v$statname b where a.statistic# = b.statistic# 
   and s.sid = a.sid and b.name = 'session cursor cache hits') session_cursor_cache_hits,
  (select value from V$sesstat a, v$statname b where a.statistic# = b.statistic# 
   and s.sid = a.sid and b.name = 'session cursor cache count') session_cursor_cache_count,
  (select value from V$sesstat a, v$statname b where a.statistic# = b.statistic# 
   and s.sid = a.sid and b.name = 'opened cursors current') currently_opened,
  osuser, machine, terminal, program, module, action, event, state
  from v$session s
) a
order by tot_parse_count desc

  
  sid
  username
  tot_parse_count
  session_cursor_cache_hits
  session_cursor_cache_count
  currently_opened
  %
  osuser
  machine
  terminal
  program
  module
  action
  event
  state
  
      137
    ALDO
    13219
    56682
    1848
    6
    428,791890460701
          PC-root
                            SQL*Net message from client
    WAITING
  

First of all bear in mind mind that you may dramatically reduce the value of session_cursor_cache_count with a good 
instance tuning, this must be done before. See Tuning
There is not a defined rule to tune session_cached_cursors, basically you should increase until you see
the session_cursor_cache_count go below the parameter.
In my example you see my perfectly tuned session_cached_cursors=2000, also look at the very low number of currently opened 
cursors that means the application correctly closes all cursors(...I'm the developer :))

#Or:
select (select value from V$SYSSTAT where name = 'parse count (total)') /
(select value from V$SYSSTAT where name = 'session cursor cache hits') perc_session_cursor_cache_hits
from dual

The more you raise session_cached_cursor the closest is the result to zero

Anyway is a good idea to set the session_cached_cursors also taking a look at the number of cursors opened (see above)