Error ORA-01000: maximum open cursors exceeded

Question

In Eclipse i'm getting this error: **Error ORA-01000: maximum open cursors exceeded I have already close connexion in the block finally, but i don't know why i'm getting this error, here is my code JAVA. Please help me!

 for (DossierAMO dos = null; it.hasNext();) {
            PreparedStatement ps = null;
            ResultSet rs = null;
            PreparedStatement ps2 = null;
            ResultSet rs2 = null;
            try {
                dos = (DossierAMO) it.next();   
                //PreparedStatement ps = null;
                //ResultSet rs = null;
                /*try
                 * 
                {*/
                System.out.println("Imma"+dos.getImma());
                    ps = cnnOracle.getConnexion().prepareStatement(
                            "select count(IMM_IMM_V_NUM_IMM) from d_salaire@prod_dist where SAL_C_DS =21 and IMM_IMM_V_NUM_IMM =?",
                            ResultSet.TYPE_FORWARD_ONLY,
                            ResultSet.CONCUR_READ_ONLY
                            );
                    ps.setString(1,dos.getImma());
                    rs = ps.executeQuery();
                    if (rs.next()){

                        if (rs.getInt(1) != 0)
                            //System.out.println("> Ecriture des dossiers d'indus dans le fichier d'indus2");
                            fichierIndius2.ecrireDossier(dos);
                        else
                        {


                            ps2 = cnnOracle.getConnexion().prepareStatement(
                            "select count(DOS_N_NUM_DOS) from d_dossier@prod_dist where IMM_IMM_V_NUM_IMM =?",
                            ResultSet.TYPE_FORWARD_ONLY,
                            ResultSet.CONCUR_READ_ONLY
                            );
                            ps2.setString(1,dos.getImma()); 
                            rs2 = ps2.executeQuery();
                            if (rs2.next()){

                            if (rs2.getInt(1) != 0)

                            fichierIndius2.ecrireDossier(dos);

                            else{
                fichierIndius.ecrireDossier(dos);
        }}}}
            } catch (Exception ex) {
                requete.fermer();

                fichierIndius.fermerSansException();
                fichierIndius2.fermerSansException();
                cnnAS400.fermerConnexion();
                cnnAS400FO.fermerConnexion();
                cnnOracle.fermerConnexion();
                System.err
                        .println("Erreur d'écriture dans le fichier d'indus! /        EXC : "
                                + ex);
                return;
            }
            finally{

                if (rs != null)
                    try {
                        rs.close();
                    } catch (Exception exx) {
                    }
                if (rs2 != null)
                    try {
                        rs2.close();
                    } catch (Exception exx) {
                    }
                if (ps != null)
                    try {
                        ps.close();
                    } catch (Exception exx) {
                    }
                if (ps2 != null)
                    try {
                        ps2.close();
                    } catch (Exception exx) {
                    }
            }






        }

Answer 1

You are (or were) setting rs and ps to null in the else, i.e. when rc.getInt(1) == 0. That meens that when you get to the finally block, these tests would fail, and ps and rs could not be closed:

            if (rs != null)
                try {
                    rs.close();
                } catch (Exception exx) {
                }
            ....
            if (ps != null)
                try {
                    ps.close();
                } catch (Exception exx) {
                }

As @APC already pointed out, you are also (or were) closing the wrong statement here:

            if (ps2 != null)
                try {
                    ps.close();
                } catch (Exception exx) {
                }

The bigger problem is that you don't free your connection(s) as you claimed. There is a call to cnnOracle.fermerConnexion(), but only in an exception handler:

...
        } catch (Exception ex) {
            requete.fermer();

            fichierIndius.fermerSansException();
            fichierIndius2.fermerSansException();
            cnnAS400.fermerConnexion();
            cnnAS400FO.fermerConnexion();
            cnnOracle.fermerConnexion();
            System.err
                    .println("Erreur d'écriture dans le fichier d'indus! /        EXC : "
                            + ex);
            return;
        }
        finally{
...

You need to close the connection in the finally block as well, after you (correctly) close the statements and result sets.

You also need to look at what cnnOracle.fermerConnexion() is doing. You call cnnOracle.getConnexion() twice, once for each of your prepared statements. If those return different connections, and fermerConnexion() only closes one, you're leaking there too. You'll need to investigate what happen inside each of them.

It would be more normal to use one connection, so you have a variable called say conn that you set with cnnOracle.getConnexion(), and then create your prepared statments as ps = conn.prepareStatement(...).

You currently seem to be getting connections and recreating and destroying the prepared statements inside a loop. It would be much more efficient to get the connection and prepare the statements once before the loop, and just execute them inside the loop. And then close the prepared statements and connection after the loop completes (and still in the exception handler, since that returns to the caller, and the finally block for the try block will no longer close them).

Answer 2

" i don't know why i'm getting this error"

Could it be this typo?

            if (ps2 != null)
                try {
                    ps.close();
                } catch (Exception exx) {
                }

You're closing ps not ps2