Search code examples
c#oracle-databasedatabase-connectionconnection-poolingdevart

Clear connection pooling in devart?


I have a Windows service application that connects to database every minutes. Somethimes i get this error:

ORA-20110: ORA-06508: PL/SQL: could not find program unit being called

Solution is close and reconnect database in plsql but i want to do this in application side.

So i need to disable all connection poolings and try to reconnect database. How can i do this using devart ?

This is my connection

this.ConnectMode = OracleConnectMode.Default;
this.Direct = true;
this.Close();
this.Server = ConfigurationManager.AppSettings["db_hostname"];
this.Port = Convert.ToInt32(ConfigurationManager.AppSettings["db_port"]);
if (db_connection_type == "SID")
   this.Sid = ConfigurationManager.AppSettings["db_sid"];
else
   this.ServiceName = ConfigurationManager.AppSettings["db_sid"];
this.UserId = db_username;
this.Password = db_password;
this.Open();

Solution

  • I found clearPool of oracleconnection ownclass

    // C#
    // Sample demonstrating the use of ClearPool API in OracleConnection class
    
    using System;
    using Oracle.DataAccess.Client;
    
    class ClearPoolSample
    {
      static void Main()
      {
        Console.WriteLine("Running ClearPool sample..." );
        // Set the connection string
        string strConn = "User Id=scott;Password=tiger;Data Source=oracle;" +
                         "Min pool size=5;";
        OracleConnection conn = new OracleConnection(strConn);
    
        // Open the connection
        conn.Open();
    
        // Clears the connection pool associated with connection 'conn'
        OracleConnection.ClearPool (conn);
    
        // This connection will be placed back into the pool
        conn.Close ();
    
        // Open the connection again to create additional connections in the pool
        conn.Open();
    
        // Create a new connection object
        OracleConnection connNew = new OracleConnection(strConn);
    
        // Clears the pool associated with Connection 'connNew'
        // Since the same connection string is set for both the connections,
        // connNew and conn, they will be part of the same connection pool.
        // We need not do an Open() on the connection object before calling
        // ClearPool
        OracleConnection.ClearPool (connNew);
    
        // cleanup
        conn.Close();
        Console.WriteLine("Done!");
      }
    }