SQL & PL/SQL

Hi Guys,

We have a J2EE application calls Oracle stored procedures(REF CURSORS) to access DB, we will experience "Max Number of cursors exceeded" error after a time period, arround 2 or more weeks running. I checked the DB site, found the number of opened cursors kept growing up while the application was running, but we already closed the resultset/statement/connection in the Java cocde after each stored procedure call.
I did some further investigations, found two problems -
1. sql "SET TRANSACTION READ ONLY" in the stored procedure can open a cursor and no way to close it, unless reset the
connection pool at Java Application Server side.
I already removed all the "SET TRANSACTION READ ONLY"/"COMMIT" in the S.P. to remove the related implicit opened cursors.
See the Stored Procedure source code below:

PROCEDURE CustomerCountByPhase
(
curs1 IN OUT RESULT_SET,
OpsCentreID IN ons.dstrbtn_eqpmnt.oprtns_cntre_id%TYPE,
EquipmentNo IN ons.dstrbtn_eqpmnt.eqpmnt_nmbr%TYPE
)
IS
BEGIN
SET TRANSACTION READ ONLY;
OPEN curs1 FOR
SELECT phse_ind, COUNT(*) as CCount FROM cstmr_cnnctn_v
WHERE eqpmnt_id IN
(
SELECT eqpmnt_id FROM ons.upstrm_cnnctn
START WITH upstrm_eqpmnt_id IN
(SELECT eqpmnt_id FROM ons.dstrbtn_eqpmnt
WHERE eqpmnt_nmbr=EquipmentNo AND oprtns_cntre_id=OpsCentreID)
CONNECT BY PRIOR eqpmnt_id = upstrm_eqpmnt_id
UNION
SELECT eqpmnt_id FROM ons.dstrbtn_eqpmnt
WHERE eqpmnt_nmbr=EquipmentNo AND oprtns_cntre_id=OpsCentreID
)
GROUP BY phse_ind;
COMMIT;
END CustomerCountByPhase;

2. Each SQL command, Select/Insert/Update/..., in the stored procedure can open its own cursor and never close it
untill reset the connection pool.
See the following code, Oracle will open three cursors for Select COUNT(*).../Insert INTO.../Update... commands.

PROCEDURE CustomerOutageArmBy1Phase
(
OutageID IN ons.cstmr_outge.outge_id%TYPE,
ModifiedBy IN ons.cstmr_outge.last_updte_by%TYPE,
ModifiedDTTM IN ons.cstmr_outge.last_updte_dt_tme%TYPE,
Phase IN ons.dstrbtn_eqpmnt.phse_ind%TYPE,
Status OUT number
)
IS
BEGIN
DECLARE
RCount number(10);
BEGIN
SELECT COUNT(*) INTO RCount FROM ons.cstmr_outge WHERE outge_id=OutageID;
IF RCount=0 THEN
INSERT INTO ons.cstmr_outge(bill_accnt_nmbr, outge_id, twnshp_nme, area_cd, phne_nmbr, outge_stats_ind, outge_dt_tme,
estmtd_rstrtn_dt_tm, last_updte_by, last_updte_dt_tme)
SELECT T1.bill_accnt_nmbr, OutageID outge_id, T1.twnshp_nme, T1.area_cd, T1.phne_nmbr, T2.outge_stats_ind,
T2.outge_dt_tme, T2.estmtd_rstrtn_dt_tm, ModifiedBy last_updte_by, ModifiedDTTM last_updte_dt_tme
FROM ons.cstmr T1, ons.outge T2
WHERE T2.outge_id=OutageID
AND
T1.bill_accnt_nmbr
IN
(SELECT DISTINCT bill_accnt_nmbr FROM ons.cstmr_cnnctn T1, ons.dstrbtn_eqpmnt T2
WHERE T1.eqpmnt_id IN
(SELECT eqpmnt_id FROM upstrm_cnnctn
START WITH upstrm_eqpmnt_id IN
(SELECT eqpmnt_id FROM ons.outge WHERE outge_id=OutageID)
CONNECT BY PRIOR eqpmnt_id = upstrm_eqpmnt_id
UNION
SELECT eqpmnt_id FROM ons.outge WHERE outge_id=OutageID
)
AND
T1.eqpmnt_id=T2.eqpmnt_id AND INSTR(T2.phse_ind, Phase)>0
);
Status := SQL%ROWCOUNT;
UPDATE ons.outge SET ivr_set_flg='Y' WHERE outge_id=OutageID;
COMMIT;
END IF;
END;
END CustomerOutageArmBy1Phase;

///I typed in the following SQL to check the opened cursors, the result is///
select count(*), sql_text from v$open_cursor group by sql_text order by count(*)

COUNT(*) SQL_TEXT
------------------------------------------------------------------------------
...
7 INSERT INTO ONS.CSTMR_OUTGE ( BILL_ACCT_NMBR, OUTGE_ID,TWNSH...
7 SELECT COUNT(*) FROM ONS.CSTMR_OUTGE WHERE OUTGE_ID = :b1...
7 UPDATE ONS.OUTGE SET IVR_SET_FLG="Y" WHERE OUTGE_ID = :b1...
93 SET TRANSACTION READ ONLY


My questions are:
1. Why "SET TRANSACTION READ ONLY " will open a new implicit cursor ?
2. Why Oracle opened implicit cursors for each of above SQLs I indicated in the problem #2 and never close them, even after Java application closed ResultSet/Statement/Connection ? How can I close them ?

Thank you very much !

James Lee