I am getting an ORA-01000 SQL exception. So I have some queries related to it.

1. Are maximum open cursors exactly related to number of JDBC connections, or are they also related to the statement and resultset objects we have created for a single connection ? (We are using pool of connections)
2. Is there a way to configure the number of statement/resultset objects in the database (like connections) ?
3. Is it advisable to use instance variable statement/resultset object instead of method local statement/resultset object in a single threaded environment ?

4. Does executing a prepared statement in a loop cause this issue ? (Of course, I could have used sqlBatch) Note: pStmt is closed once loop is over.

   { //method try starts  
     String sql = "INSERT into TblName (col1, col2) VALUES(?, ?)";
     pStmt = obj.getConnection().prepareStatement(sql);
     pStmt.setLong(1, subscriberID);
     for (String language : additionalLangs) {
       pStmt.setInt(2, Integer.parseInt(language));
       pStmt.execute();
     }
   } //method/try ends
   
   { //finally starts
      pStmt.close()
   } //finally ends 
   

5. What will happen if conn.createStatement() and conn.prepareStatement(sql) are called multiple times on single connection object ?

Edit1: 6. Will the use of Weak/Soft reference statement object help in preventing the leakage ?

Edit2: 1. Is there any way, I can find all the missing "statement.close()"s in my project ? I understand it is not a memory leak. But I need to find a statement reference (where close() is not performed) eligible for garbage collection ? Any tool available ? Or do I have to analyze it manually ?

Please help me understand it.

Solution

To find the opened cursor in Oracle DB for username -VELU

Go to ORACLE machine and start sqlplus as sysdba.

[oracle@db01 ~]$ sqlplus / as sysdba 

Then run

SELECT   A.VALUE,
    S.USERNAME,
    S.SID,
    S.SERIAL#
  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 USERNAME     = 'VELU';

If possible please read my answer for more understanding of my solution