Using ODP.Net with Oracle 9 client (connection to Oracle 10 databases)

There are 2 types of error situations I would like to be able to differentiate between: 1. Connection errors (net result = Oracle not available, a reasonably critical situation...) 2. Other errors (probably user input related, zero rows returned isn't an error, the key is that Oracle is functioning)

If I was creating the Connection myself this would be easy (catching the exception on connection creation would be the difference).

The specific situation I'm working with is calling a stored procedure.

The connection is created and the query performed all inside this line of code: dataSet = RetrievalProcedures.ByStandardInsuredSearch(surname, firstName, dateOfBirth);

Is there a way to determine if the error was connection related, or do I have to filter through the possible Oracle exceptions to be sure?

Thanks, Jason

Hello,

you can catch the exception and make the difference between the type of the exception. There are many types of ORM Exception so you should distinct the difference between your problems.

In this case I'm dealing with OracleExceptions (where the message is ORA-XXXX...), passed to me from LLBLGen.

I'm just trying to figure out if there's a way to tell if it's a connection error, or an error associated with the query/SP call. Query/SP calls can be mitigated, connection errors means the system is down.

I'm considering a nasty solution where I verbosely list critical oracle errors, and check the list on OracleExceptions coming out of LLBLGen.

As far as I can tell there's just one OracleException, and LLBLGen is passing that rather than any ORM error (which is what I would expect), so the variety of ORM errors doesn't assist me in this situation.

Isn't there an inner exception/message where you can check the returned ORA-XXXX?

I will close out this thread with a little more explanation and describe the solution I'm using.

I need to be able to differentiate between a "critical" Oracle error (can't access data, DB down, bad password, etc) and data related errors (constraint issue). The key is whether data can be accessed, if it can't I consider that "critical".

Now, in a situation where I was coding the data layer I would catch "critical" errors when creating the Connection to Oracle. I would catch other areas executing queries against the database.

LLBLGen handles connection creation and query execution in one line of from the developer's perspective like this: DataSet dataSet = RetrievalProcedures.ByNationalIdentifier(nationalIdentifier);

So I can't catch a Connection error, if an Exception occurs the line above returns the OracleException, but I can't determine the context of when the exception occured (either on connection or on query execution).

So, how do I determine if there is a Critical Oracle Exception? I have to check the OracleException.Number against a known list of critical errors (1017 - bad name or password, 12154 - TNS server name not found, those sort of things), and if it is one of those I throw a custom CriticalOracleException which I can handle in a custom manner.

Here's the function I'm using to make this determination. LoadCriticalOracleErrorList loads a list of errors from a text file (can't put it on the DB...) using a StreamReader.

This solution works fine and it's what we are implementing in our Proxy and Service components to handle OracleExceptions coming out of our generated Oracle code.

public static Exception HandleOracleException(OracleException e, string source) {

List<int> CriticalOracleErrors = LoadCriticalOracleErrorList();

if (CriticalOracleErrors.Contains(e.Number))
{

    CoreIntegration.HandleException(e, TraceEventType.Critical);

    throw new CriticalOracleException(string.Format("Critical Oracle Error in '{0}', the eWarp is probably not functioning at this time (the Oracle error is '{1}')",source, e.Message), e);
}
else
{
    CoreIntegration.HandleException(e, TraceEventType.Error);
    throw e;
}

}

Thanks, Jason

Thanks for the heads up. It's indeed a bit of a hassle...