31 Mar 2016

Setting Oracle Managed Driver Parameters

When Microsoft released .NET4, “System.Data.OracleClient” was deprecated. When you upgrade .NET framework, you’ll probably remove the references to “​System.Data.OracleClient” and reference Oracle Managed Driver instead. On one of my project, I had to face an issue caused by a difference when calling a stored procedure in passing parameters between the Microsoft and Oracle provider.

To add a parameter, you set its:

  • name,
  • type,
  • size,
  • direction,
  • value.

By default, Microsoft provider bind parameter by name (as done with SQL Server). However by default, Oracle SGBD do not consider the parameter name but parameter position in the array of paramters.

To illustrate, here is a simple stored procedure :

The following code will throw the coded exeption :

To solve this issue, the class OracleCommand expose the BindByName property. In this case, you need to set it to true.

As there are no configuration to set by default this property to true, you need to set BindByName to true on each OracleCommand creation.

The following code will behave as expected, paramOut value will be 3 :

When upgrading System.Data.OracleClient, if you have issues when calling your Oracle database, just think about this little boolean property.