App hangs on ExecuteNonQuery on Stored procedure

[awaitzbe]

Hi,
I am running a WPF client with Entity Framework that connects to a Oracle Server 11.1.0.7, using dotConnect for Oracle 6.30.185. The connection is in Direct Mode and ConnectionTimeout = 0.

Sometimes, when launching the program from deployed version (not in debugging mode) the app hangs / freezes when calling Stored procedures which calls command.ExecuteNonQuery(). The app never restore again. This started to happen only after I upgraded dotConnect for Oracle to 6.30.xx.

When attaching a debugger, the error says:
{"Network error:: An established connection was aborted by the software in your host machine"}
"Network error: 207"

or other Network problem.

The Call Stack says:
System.Data.Entity.dll!System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands
(System.Data.EntityClient.EntityCommand entityCommand, System.Data.CommandBehavior behavior) + 0x1e0 bytes
System.Data.Entity.dll!System.Data.EntityClient.EntityCommandDefinition.Execute
(System.Data.EntityClient.EntityCommand entityCommand = {System.Data.EntityClient.EntityCommand}, System.Data.CommandBehavior behavior) + 0x4c bytes
System.Data.Entity.dll!System.Data.EntityClient.EntityCommand.ExecuteReader(System.Data.CommandBehavior behavior = SequentialAccess) + 0x2d bytes
System.Data.Entity.dll!System.Data.EntityClient.EntityCommand.ExecuteScalar(System.Func resultSelector = {Method = {System.Reflection.RuntimeMethodInfo}}) + 0x2a bytes
System.Data.Entity.dll!System.Data.EntityClient.EntityCommand.ExecuteNonQuery() + 0x5c bytes
opsecdb1.exe!SecModel.SecEntities.Addblob(decimal? REQ_DETAIL_ID, byte[] REQ_RAW_DATA, ref decimal? CUR_ID = null) + 0x1a9 bytes

Can you help me on that? Should I downgrade the dotConnect version?

Thanks!

[awaitzbe]

On the latest revision history, it says: "Freezing of the GC finalizer thread at unexpected connection closure while reading XMLType is fixed". Is there any connection between the 2 problems?

[Shalex]

1. Try setting the "Default Command Timeout=0;" connection string parameter.
2. Can you reproduce the problem via plain ADO.NET (not via Entity Framework)?
3. Please send us the corresponding DDL/DML script to execute your stored procedure in our environment.
4. Specify your connection string (roughly, without credentials).

[awaitzbe]

This is my connection string.

Code: Select all

private string BuildConnectionString(string password)
{
    OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
    oraCSB.Direct = true;
    oraCSB.Port = 1529;
    oraCSB.Sid = "DEPTDB";
    oraCSB.UserId = Environment.UserName.ToLower();
    oraCSB.Password = password;
    oraCSB.Unicode = true;
    oraCSB.PersistSecurityInfo = true;
    oraCSB.Server = "localhost";
    oraCSB.ConnectionTimeout = 0;
    oraCSB.DefaultCommandTimeout = 0;

    // Initialize the EntityConnectionStringBuilder.
    EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
    entityBuilder.Provider = "Devart.Data.Oracle";
    entityBuilder.ProviderConnectionString = oraCSB.ConnectionString;

    // Set the Metadata location.
    entityBuilder.Metadata = @"res://*/DEPTModel.csdl|
                               res://*/DEPTModel.ssdl|
                               res://*/DEPTModel.msl";
    return entityBuilder.ToString();
}

Note the DefaultCommandTimeout = 0 did not solve the problem.
Also sent you the corresponding DDL/DML script with stored procedure to reproduce in your environment.

[Shalex]

We have answered you by e-mail.

[awaitzbe]

OCI mode won't help because my customers won't install Oracle client in their machines.

But I think that since the server is auditing stored procedures, it might be causing the problem.
AUDIT_TRAIL=XML, EXTENDED

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS;
AUDIT NOT EXISTS;

When I made:
NOAUDIT EXECUTE PROCEDURE;
It seems to work.... I will keep looking whether it really solved the problem.

[SUKHEN]

could you please tell me how you resolved your issue. I have the same problem while running ExecuteNonQuery application hang and control never come back to next line for ExecuteNonQuery.

[Pinturiccio]

You are using the Direct mode, aren't you?

"Network error" is an internal error of our provider, which says "connection is not established" or "unexpected server response". It may be caused by the following reasons:
a) code tries to use a connection, which was previously closed or being currently used in another thread;
b) a server terminates the connection (server's problem);
c) a bug in the provider.

General recommendations:
1) avoid using the same OracleConnection in several threads at the same time because an OracleConnection instance is not guaranteed to be thread safe;
2) try using the "Validate Connection=true;" connection string parameter to validate connections that are being taken from the pool;
3) upgrade to the latest (8.4.359) version of dotConnect for Oracle. It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
4) try using the OCI mode (via Oracle Client).

We need to reproduce the problem in our environment to find out the true reason. If possible, try to localize the issue and send us a small test project with the corresponding DDL/DML script.