Tom,
"(ignore any static SQL from PLSQL routines -- its cached on purpose)."
What is meant by static SQL statements in your followup above.

My client is complaining that, they are getting "maximum cursors exceeded" error.

At what scenarios, all the opened cursors gets closed.Will this closing of opened cursors depends on time too.

My Database is Oracle: 8.1.7
Apllication Server: Tomcat.

Am sure at all places in java, they are closing resultsetsa and connections properly.
At SP level we are not closing refcursors.Data extraction and closing of ResultsSet is doing at Java level.

When I tried with the following example, I did not get the complete picture about the 'Opened Cursors" issue.

Please look at the examples.

conn SWIPTEST2/SWIPTEST2

create or replace procedure sptest3 is
n1 NUMBER;
BEGIN
select count(*) into n1 from tab;
insert into test_tab values(32);
commit;
end ;
/

SQL>exec sptest3;

SQL> conn sys/sysdba

SQL>select sql_text from v_$open_cursor where user_name = 'SWIPTEST2' ;

SQL_TEXT
------------------------------------------------------------
 select /*+ CHOOSE */ a1.constraint_name,   c1.column_name,
INSERT INTO TEST_TAB VALUES ( 32  )
Select /*+ CHOOSE */ cols.column_name as Name, nullable,  da
Select /*+ CHOOSE */ object_name, object_type, status from
SELECT COUNT(*)   FROM TAB
BEGIN sptest3; END;
SELECT rowid, "SWPTEST2"."T_WIPS_INVOICE_CHARGE_CODE".* FRO

SQL>7 rows selected.

when I query the v_$open_cursor, it is giving the above results.

v_$open_cursor view will give all kind of cursor list(implcit, explicit, refcursors).?

"INSERT INTO TEST_TAB VALUES ( 32  )" is this a static sql.?
In the v_$Opnen_Cursor count the above statement also counted.?
We set our open_cursors to 200. Is above statement also consuming a cursor from the above 200 value.

Please give me your suggestion to get rid of this problem.

thanks in advance.