ODP.NET hang on Insert with return value within a Transaction

Introduction

So, you are finding that ODP.NET seems to be hanging on a second insert within a loop which is part of a transaction.. and it’s driving you crazy yeah? The only thing that you notice is that it is essentially the same insert statement each iteration of the loop for a valid business reason; and the insert statement is perfectly valid (possibly even tested by running the SQL manually using TOAD, SQL+ or SQL Developer etc)

The issue

The issue is actually a bug within ODP.NET where two identical insert statements are being ran within the same transaction scope with more importantly the same return parameter name.

A basic example

Within the Transaction Scope as below

using (var transScope = new TransactionScope())
{
	// .. loop here
}

There is a loop which inserts the same values twice for a legitimate business reason (the code below added only as a basic example)

string sql = "INSERT INTO MONITORING (DESCRIPTION) VALUES ('Program Running') Returning MONITORID into :return_val";
int monitorId;

using (OracleParameter ret = new OracleParameter("return_val", OracleDbType.Int32, ParameterDirection.ReturnValue))
{
	        // create db connection etc

        	com.Parameters.Add(ret);
                var output = database.ExecuteNonQuery(com);

                // get the return value
        }
}

You find that the second insert hangs for no obvious reason on the ExecuteNonQuery line… This is a bug with ODP.NET which still has not been resolved.

The workaround

Well the basic way to resolve this issue is to change the name of the return parameter each time within the loop to workaround the bug..

simply create a GUID in the code using:

Guid.NewGuid()

Then trim it, remove any dashes, and take a substring (I went for 6 chars) and then give the Oracle return parameter that name within the SQL statement and the Oracle Parameter object. i.e it will end up looking something like:

return_5eg56k

This means fundamentally the two insert statements and return parameters are no longer identical in the eyes of ODP.NET within that transaction connection and everything will work as expected..

Until the bug is fixed this seems to be the only reasonable workaround to this specific issue… hope it saves somebody else a headache somewhere.