Connection pool and ORA-01000

Question

I created an asynchronous ExecuteQuery function that is able to execute query asynchronously so that if ever it's called inside a loop, the next query waits for the former one to finish before being executed. The doCreatePool function is called once, at program initialization. It creates a connexion pool and saves it in the Global variable config.Oracle_POOL so that the connexion pool remains alive, even after then doCreatePool returns.

Socket.io events call ExecuteQuery to execute queries (either one or more in a loop).

Everything goes right but, after a certain amount of queries executed, I get an ORA-1000 error: "Error: ORA-01000: maximum open cursors exceeded" either for select, update, and insert queries.

Normally the Connexion pool is supposed to avoid this issue?

What is wrong with my code?

doCreatePool (); //Create a connexion pool and save it in config.Oracle_POOL
//dbConfig={....}

function doCreatePool () {
    dbConfig=config.Settings.DataBaseConnexionString;
    oracle.createPool (
      {
        user          : dbConfig.user,
        password      : dbConfig.password,
        connectString : dbConfig.connectString,
        poolMax       : 44,
        poolMin       : 2,
        poolIncrement : 5,
        poolTimeout   : 4
      },
      function(err, pool)
      {
        if (err) {
          console.error("createPool() callback: " + err.message);
          return;
        } else {
            config.Oracle_POOL = pool;
        }
     });      
}

function ExecuteQuery(Query, LstParam, callBack) {

    config.Oracle_POOL.getConnection( function(err, connection) {
        if (err){ 
            console.log("Oracle connexion pool --> err --> ", err);
            if (connection)
            connection.release(
                function(err) {
                    if (err) {
                        console.log("(1) connection.release --> err--> ",err)
                    }
                }
            );  
            callBack(err, { rows : [] });   
        } else {
            var OracleQueryOptions={outFormat: oracle.OBJECT, maxRows : 500, autoCommit : true};
            connection.execute(Query, LstParam, OracleQueryOptions , function(err, results) {
                if (!err) {
                    console.log("* connection.execute  --> err--> ",err, "Query --> ", Query);
                }
                connection.release( function(err2) {                            
                        if (err2) {
                            console.log("(2) connection.release --> err-->",err)
                        }
                        callBack(err, results);
                    }
                );
            });
        }
    });     
            
        
}

Answer 1

I know where my problem comes from. By adding these lines of code before calling the getConnexion() function

// NUMBER OF CONNCETIONS OPEN
console.log("B4getConnexion -> ORACLE: CONNX OPEN: " + config.Oracle_POOL.connectionsOpen);

// NUMBER OF CONNEXTIONS IN USE
console.log("B4getConnexion -> ORACLE: CONNX IN USE: " + config.Oracle_POOL.connectionsInUse);

and this at the end of the getConnexion() function call :

// NUMBER OF CONNCETIONS OPEN
console.log("AFTER/getConnexion -> ORACLE: CONNX OPEN: " + config.Oracle_POOL.connectionsOpen);

// NUMBER OF CONNEXTIONS IN USE
console.log("AFTER/getConnexion -> ORACLE: CONNX IN USE: " + config.Oracle_POOL.connectionsInUse);

I have notice that config.Oracle_POOL.connectionsOpen increses until it reaches poolMax (44). This is why I get erros like (this is my opinion for now) :

ORA-24418: Cannot open further sessions
ORA-24418: Cannot open further sessions
ORA-01000: maximum open cursors exceeded

How can I avoid config.Oracle_POOL.connectionsOpen to increase ?. Please note that console.log inform that RelaseConnexion is successfully called after ExecuteQuery and then getConnexion. That all sequence are executed sequentially.

If I don't use release connexion (which mean by executing several statement in the loop) config.Oracle_POOL.connections stay stable. But at the end of the loop I clause the connexion, and when an event occur, it starts a new loop and I create a new connexion, then config.Oracle_POOL.connections increases...