ODP.NET : Parameter Types "cached" on identical CommandText

Question

I'm currently evaluating Oracle's ODP.NET DataProvider and I ran into a problem that popped up in one of our testcases: When the same command text is executed with different parameter types, the parameter type of the first executed command is used in all following commands.

Take for example the following code:

const int sampleInt32 = 1234567890;
const string sampleNvarchar = "someTestString";

const string sqlCommandtext = "SELECT :PARAM PARAM FROM DUAL";
using (OracleConnection connection = new OracleConnection(builder.ConnectionString))
{
    connection.Open();

    //Test 1 - Int 32
    using (OracleCommand commandInt32 = connection.CreateCommand())
    {
        commandInt32.CommandText = sqlCommandtext;
        commandInt32.Parameters.Add("PARAM", OracleDbType.Int32, sampleInt32, ParameterDirection.Input);
        using (IDataReader reader = commandInt32.ExecuteReader())
        {
            while (reader.Read())
            {
                int resultInt32 = (int)reader.GetDecimal(0);
                Assert.AreEqual(sampleInt32, resultInt32);
            }
        }
    }
    //Test 2 - NVarchar
    using (OracleCommand commandNVarchar = connection.CreateCommand())
    {
        commandNVarchar.CommandText = sqlCommandtext;
        commandNVarchar.Parameters.Add("PARAM", OracleDbType.NVarchar2, sampleNvarchar, ParameterDirection.Input);
        using (IDataReader reader = commandNVarchar.ExecuteReader())
        {
            while (reader.Read())
            {
                string resultNVarchar = reader.GetString(0);
                Assert.AreEqual(sampleNvarchar, resultNVarchar);
            }
        }
    }
}

If commandInt32 is executed before commandNVarchar, execution of commandNVarchar fails with ORA-01722 - Invalid number. If the order is switched so commandNVarchar is executed first, it fails with "Specified cast is not valid" on reader.GetDecimal.

So far I've tried setting StatementCacheSize=0; Pooling=false; StatementCachePurge=true as ConnectionString parameters but I can't get this to work.

Is there anything I'm missing or are there any other options worth trying?

EDIT: Maybe some background on why this is needed/required: We don't use ODP or any other Dataprovider directly in our application (or at least: we're on our way to reach this goal), there's an DataLayer in between that performs database/provider specific optimiziations and monitoring of connection health,...

In this Layer for example StoredProcedures can be called, having the option of parameter type tuning. Some of our procedures have Clobs as Parameter types, as sometimes the value can be longer than x characters, but most likely it will be shorter. So before executing via ExecuteNonQuery with ArrayBindCount set to y, parameter values are checked if Clob can be passed as varchar (Nclob as Nvarchar). "Rebinding" reduces the time to execute 2500 records from about 500ms to 200ms at the cost of losing a few ms checking string lengths. And this rebinding can only be done if the parameter type can be changed. Without this option we would need to execute it as Clob everytime, taking the performance hit.

Answer 1

To my understanding, parameter binding is unsupported in a SELECT list. I was so surprised that this worked at all that I had to run your code to see it with my own eyes. I believe that for the client to allow that SQL statement to execute at all is a bug.

Regardless, I inserted the following line between the test cases to get them both to work:

connection.PurgeStatementCache();

However, this only seems to work with the Managed Client (I've tried it with version 4.112.3.60). The regular client still fails as you describe.

Answer 2

Two things. When used as connection string parameters, the configuration variables need to have spaces, ie

Statement Cache Size=0;

The format you are using can be used directly in the config though: http://docs.oracle.com/html/E10927_01/featConfig.htm#CJABCACG

http://docs.oracle.com/html/E10927_01/featOraCommand.htm#CIHCAFIG

You could use that same configuration section to enable tracing - comparing the traces might give you an idea of what is happening.

I believe PurgeStatementCache (not sure StatementCachePurge exists) is a runtime command, ie

connection.PurgeStatementCache

Answer 3

Metadata Pooling = false;

Our application is using Oracle 12c with ODP.Net Managed Provider

When using OracleCommandBuilder.DeriveParameters() we were always seeing the same parameters return from the stored procedure despite adding/ removing/ updating parameters. We would only see the changes after restarting the IIS process.

The only solution that worked was setting Metadata Pooling = false; in the Oracle connection string

We had no success with the following which have been mentioned here or on Oracle's forums:

connection.PurgeStatementCache();

Statement Cache Size=0;

Pooling = false;

Answer 4

What version of Oracle are you connecting to? This may be a bind variable peaking (or lack thereof) issue. The feature was introduced in 9i but had some issues all the way thru 10. You could try executing the following to see if you can reproduce the problem without ODP.net:

var param varchar2(255)
exec :param:='TEST';
select :param FROM DUAL;

change the type on "param" from varchar2 to number and change the value and reexecute to see what happens.

You could also try executing the command under a different connection instead of a shared one.

In the end, you could simply rename the bind variable in the statement, relative to the type (ie :paramNum or :paramString). The name you give the the parameter on the .net side is irrelevant unless cmd.BindByName is set to true. By default it is false, and variables are bound in the order they are added.