ODP.NET Tip: Bind Variables and the BindByName Property

Note: For a brief introduction to bind variable usage in an ODP.NET application, see my September/October 2005 Oracle Magazine article "The Values That Bind".

When using bind variables in the CommandText property of an OracleCommand object, it is important to understand the meaning of the value of the BindByName property for the OracleCommand object. This property is a read/write boolean property which defaults to false. The fact that this property defaults to false means that ODP.NET operates, by default, in what is known as "bind by position" mode. In this mode, when OracleParameter objects are added to the parameters collection for an OracleCommand object, they must be added to the collection in the same order in which they appear in the CommandText property.

For example, say you have the following:

// assumes cmd is an object of type OracleCommand
cmd.CommandText = "select employee_id from hr.employees where first_name = :1 and last_name = :2"

In this sample, there are 2 bind variables used (denoted by :1 and :2 in the text). Using the default setting (false) for the BindByName property this means that the OracleParameter object for the first_name placeholder should be added to the parameters collection first followed by the OracleParameter object for the last_name placeholder.

In contrast to this, if you set the BindByName property to true, you may add the OracleParameter objects to the parameters collection in any order you wish. Of course, if you set the property to true, you also need to ensure that the ParameterName property of the OracleParameter object matches the name used in the CommandText (without the ":").

Another area where the value of the BindByName property may be the source of confusion is when using a PL/SQL function. For example, say you have a function that returns a value and accepts 2 parameters as input. When you use this function in your code, the following pseudo-code represents what is sent to the database:

:return_value := some_function (:parameter1, :parameter2)

Notice that the return value is the first bind variable used. Therefore, in bind by position mode (the default!), you must add the OracleParameter object for the return value to the parameters collection first (not last!), followed by the parameter object for "parameter1" and then "parameter2".