C# ORA-01012: not logged on

Question

I've made the connection to the Oracle DB in my .net Core API project

I use this driver for connection to Oracle

My code:

connection.Open();

using (OracleCommand cmd = connection.CreateCommand()) {
  cmd.CommandType = CommandType.StoredProcedure;

  cmd.CommandText = "MyPackage.SP";
  cmd.Parameters.Add(DbParam(cmd, "id", OracleType.Number, 1));
  cmd.Parameters.Add(DbOutParam(cmd, "out_key", OracleType.VarChar, 1024));

  try {
    cmd.ExecuteNonQuery();
    result = cmd.Parameters["out_key"].Value.ToString();
  } catch (OracleException oe) {
    Console.Out.WriteLine("OracleException: {0}: {1}", Tool.GetCurrentMethod(), oe.Message);
  }

  connection.Close();
}

My connection string:

Data Source = ORACLE-DEV:1521/pdb;PERSIST SECURITY INFO=True;USER ID=user; Password=password;

Sometimes the connection disappears, and after I've got for all connections one exception:

ORA-01012: not logged on
Process ID: x
Session ID: y Serial number: z

I've already read question but there is a user solved the problem by setting to true the "Validate Connection" and also stay warning

As a warning, I quote the Oracle docs.

The Validate Connection attribute validates connections coming out of the pool. This attribute should only be used when absolutely necessary because it causes a server round-trip to the database to validate each connection right before it is provided to the application. If invalid connections are uncommon, developers can create their own event handler to retrieve a new connection, rather than using Validate Connection. This generally provides better performance.

I want to know what to do after catch exceptions like that and how to do reconnect automatically

try {
  cmd.ExecuteNonQuery();
} catch (OracleException oe) {
  Console.Out.WriteLine("OracleException: {0}", oe.Message);
  if (oe.Code == -1012) {
    // put your code here
  }
}

Any ideas?

Answer 1

I wouldn't retry executing the query after an exception has been caught, that solution can make your application potentially slow and unstable.

Your code seems simple enough to be working using the official driver so why using that unofficial driver your are using?

Try with the latest Oracle driver for .NET core:

First, remove the current driver, then install it using NuGet or the NuGet console:

Install-Package Oracle.ManagedDataAccess.Core -Version 2.18.3