How to reestablish an Oracle connection after a network failure?

Olivier Carneiro

Question

Hi people,

I need help on an Extention code (C#) that queries a table on Oracle (Hub Server is installed over SQL Server).
The code is working fine on normal conditions, and when a connection problem occurs (like a network failure), the query fails has expected, returning Oracle error « ORA-03113: end-of-file on communication channel », on the first attempt. Then, during the network problem, it returns « ORA-03114: not connected to ORACLE » after each query.

The problem is that when the network gets reactivated, the connection does not reestablishes, returning the same « ORA-03114: not connected to ORACLE».
The only way to reactivate the connection is to issue an «iisreset»

The code in the extension is as simple as the one described. Any ideas on how to throw the connection to the garbage, when an error occurs?
Thanks,
Olivier

public void MssSelect_Flight( string ssConnectionString, int ssLastId, out OutSystems.NssULTRAConnection.RLULTRA_FLIGHTRecordList ssFlightRL, out bool ssResult, out string ssResultMessage) {
ssFlightRL = new OutSystems.NssULTRAConnection.RLULTRA_FLIGHTRecordList( null);
ssResult = false;
ssResultMessage = "";
// TODO: Write implementation for action
ssResult = true;
OracleConnection myConnection = new OracleConnection();
myConnection.ConnectionString = ssConnectionString;

string mySelectQuery = "SELECT * FROM FLIGHT where id > " + ssLastId;
OracleCommand myCommand = myConnection.CreateCommand();
myCommand.CommandText = mySelectQuery;
OracleDataReader myReader = null;

myConnection.Open();
RCULTRA_FLIGHTRecord record = new RCULTRA_FLIGHTRecord("");

DateTime nullDT = new DateTime( 1900, 1, 1, 0, 0, 0);
try
{
myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection) ; // Always call Read before accessing data.
while (myReader.Read())
{
[… assign values to record.ssSTULTRA_FLIGHT as record.ssSTULTRA_FLIGHT.ssID = myReader.GetInt32(0); …]

ssFlightRL.Append( record);
}
}
catch(Exception e)
{
ssResult = false;
ssResultMessage = e.ToString();
}
finally
{
if (myReader != null)
myReader.Close();
//Implicitly closes the connection because CommandBehavior.CloseConnection was specified.
}
} // MssSelect_Flight

01 Aug 2006

Miguel João

Staff

Hi Olivier

According to the MSDN information, about the use of the Oracle Client DB connection objects, they recommend to always explicit close the connection. Although you have the "CommandBehavior.CloseConnection" set in your reader, please try to close it explicitly after closing the reader, since there have being several reports of the Oracle connections not being removed from the connection pool by certain automated methods (like garbage collection).

However, before that, please verify that Service Pack 1 for Microsoft .NET Framework 1.1 is installed in that Hub Node in question, since there's a known bug of Microsoft Oracle Client in https://support.microsoft.com/default.aspx?scid=kb;en-us;830173 that has the same symptoms. In order to check if the Service Pack 1 is properly installed check the full version (1.1.4322.xxxx) and it should match either 1.1.4322.2032 or 1.1.4322.2300, as stated in https://support.microsoft.com/kb/318785/ . Also, in the IIS default website properties, this should be the version registered in the ISAPI filters, or in the Web Server Extensions.

Hope this information helps.

Cheers

Miguel João

10 Nov 2006

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines