How to reestablish an Oracle connection after a network failure?

How to reestablish an Oracle connection after a network failure?

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?

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;

RCULTRA_FLIGHTRecord record = new RCULTRA_FLIGHTRecord("");

DateTime nullDT = new DateTime( 1900, 1, 1, 0, 0, 0);
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();
if (myReader != null)
//Implicitly closes the connection because CommandBehavior.CloseConnection was specified.
} // MssSelect_Flight
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;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 . 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.


Miguel João