ODP.NET: parameter types cached on an identical CommandText

Question

I am currently evaluating Oracle ODP.NET DataProvider and I am having a problem in one of our test arrays: When the same command text is executed with different types of parameters, the parameter type of the first command executed in all of the following commands is used.

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 the NVarchar command, the execution of the NVarchar command fails with ORA-01722 - Invalid number. If the order is switched so that the NVarchar command is executed first, it fails with "The specified cast is invalid" on reader.GetDecimal.

So far, I have been trying to set StatementCacheSize = 0; Accumulation = false; StatementCachePurge = true as parameters of ConnectionString, but I cannot get this to work.

Is there anything that I will skip, or are there any other options worth trying?

EDIT . Maybe some experience is why it is necessary / required: we do not use ODP or any other Dataprovider directly in our application (or at least: we are on our way to achieve this goal), there is a DataLayer between them, which performs database / provider optimization and connection health monitoring, ...

In this layer, you can call StoredProcedures, having the ability to configure parameter settings. Some of our procedures have Clobs as parameter types, because sometimes the value may be longer than the characters x, but most likely it will be shorter. Therefore, before executing through ExecuteNonQuery with an ArrayBindCount set to y, the parameter values ​​are checked if Clob can be passed as varchar (Nclob as Nvarchar). Interlacing reduces the time it takes to execute 2500 records from 500 ms to 200 ms due to the loss of several lines of test length. And this re-binding can only be done if the type of the parameter can be changed. Without this option, we will need to execute it as Clob every time, taking a performance hit.

Answer 1

As far as I understand, parameter binding is not supported in the SELECT list. I was so surprised that it worked for everyone, that I had to run your code to see it with my own eyes. I believe that for the client to allow the execution of this SQL statement is an error.

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

 connection.PurgeStatementCache(); 

However, it seems that this only works with a managed client (I tried it with version 4.112.3.60). As you describe, the regular client still fails.

Answer 2

Two things. When using connection strings as parameters, configuration variables must have spaces, i.e.

 Statement Cache Size=0; 

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

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

You can use the same configuration section to enable tracing. Comparing the trace may give you an idea of ​​what is going on.

I believe that PurgeStatementCache (not sure if StatementCachePurge exists) is an execution command, i.e.

 connection.PurgeStatementCache 

Answer 3

What version of Oracle are you connecting to? This may be a problem with the variable variable (or lack thereof). This feature was introduced in 9i, but had some problems up to 10. You can try 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 to "param" from varchar2 to a number and change the value and repeat it to find out what happens.

You can also try to run the command under a different connection, and not with a common one.

In the end, you can simply rename the bind variable to an operator, relative to the type (i.e.: paramNum or: paramString). The name you provide to the parameter on the .net side does not matter if cmd.BindByName is set to true. By default, this value is false, and the variables are linked in the order in which they are added.

Answer 4

 Metadata Pooling = false; 

Our application uses Oracle 12c with a managed provider ODP.Net

When using OracleCommandBuilder.DeriveParameters() we always saw that the same parameters are returned from the stored procedure, despite adding / removing / updating parameters. We will see the changes after restarting the IIS process.

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

We did not succeed with the following that were mentioned here or on the Oracle forums:

 connection.PurgeStatementCache(); Statement Cache Size=0; Pooling = false;