Oracle SQL query fails only in one process: "ORA-01405: fetched column value is NULL"

I'm trying to call a system stored procedure in a "plugin" that I've built. When I test my plugin out in a test application, it works fine. When I run the plugin in the targeted app I'm building it for, I get an exception from Oracle that doesn't make any sense.

I'm using Oracle server 11.2.0.1.0, and ODP.NET 2.112.2.0.

Here's the debug trace from ODP.NET from my test app:

(ENTRY) OracleConnection::OracleConnection(1)
(POOL)  New connection pool created for: "Data Source=orcl;User ID=scott;"
(ENTRY) OracleConnection::CreateCommand()
OpsSqlPrepare2():SQL: begin DBMS_AQADM.START_QUEUE(queue_name => 'MyQueue'); end;
(EXIT)  OpsSqlExecuteNonQuery(): RetCode=0 Line=877
(EXIT)  OracleCommand::ExecuteNonQuery()
(ENTRY) OracleConnection::Dispose()
(ENTRY) OracleConnection::Close()

And here's debug trace from ODP.NET from the same code running in the targeted app:

(ENTRY) OracleConnection::OracleConnection(1)
(POOL)  New connection pool created for: "Data Source=orcl;User ID=scott;"
(ENTRY) OracleConnection::CreateCommand()
OpsSqlPrepare2():SQL: begin DBMS_AQADM.START_QUEUE(queue_name => 'MyQueue'); end;
(EXIT)  OpsSqlExecuteNonQuery(): RetCode=0 Line=877
(EXIT)  OracleCommand::ExecuteNonQuery()
(ENTRY) OpsErrGetOpoCtx()
(ERROR) Oracle error code=1405; ORA-01405: fetched column value is NULL
(EXIT)  OpsErrGetOpoCtx(): RetCode=0 Line=137
(ENTRY) OracleConnection::Dispose()
(ENTRY) OracleConnection::Close()

I'm at a loss as to what could be different between the test/target applications. Both processes are running as members of 开发者_运维百科the local Administrators group. Both are using the same connection string. Both are running the same .NET code, but with a different outcome from the database server. What could be going on here?

This error comes from old OCI & prec-compiled code where an indicator variable was required to be set to indicate a null return for the field. When there is no indicator variable declared, this error is triggered if a null value is encountered. Obviously, some older code is still layered under the calls you made.

In other words, it is a data issue. I don't know where to start looking offhand.

It turns out that my target application was executing my plugin code while in Distributed XA Transaction (MSDTC in my case). The call to DBMS_AQADM.START_QUEUE has an implicit COMMIT; that I wasn't aware of. The error message is obviously not helpful at all in this case.

The solution is to wrap my call in in the following:

using (var scope = new TransactionScope(TransactionScopeOption.Surpress))
{
    // execute DBMS_AQADM.START_QUEUE code here
    scope.Complete();
}

This causes the call to DBMS_AQADM.START_QUEUE to run outside of the ambient transaction.