Oracle parameter order causing query failure

Data and Databases
1

I have an Oracle database query that works perfectly when I add parameters in a specific sequence, but fails when I change the order. Here’s my functional code:

queryString = @"SELECT * FROM COMPANY_USERS where upper(username)= upper(:username) AND company_id= :CompanyId";

myCommand = new OracleCommand();
if (database.GetConnection().State == ConnectionState.Open)
{
  myCommand.CommandText = queryString;
  myCommand.Connection = database.GetConnection();
  myCommand.Parameters.Add("username", OracleDbType.Varchar2);
  myCommand.Parameters["username"].Value = userName;
  myCommand.Parameters.Add("CompanyId", OracleDbType.Varchar2);
  myCommand.Parameters["CompanyId"].Value = companyId;
  dataReader = myCommand.ExecuteReader();
  dataReader.Read();
}

But when I switch the parameter order like this:

myCommand.Parameters.Add("CompanyId", OracleDbType.Varchar2);
myCommand.Parameters["CompanyId"].Value = companyId;
myCommand.Parameters.Add("username", OracleDbType.Varchar2);
myCommand.Parameters["username"].Value = userName;

The query stops working completely. Do Oracle parameters need to be added in ascending alphabetical order or in the same sequence as they appear in the SQL statement?

2

Interesting! Oracle’s being picky about parameter positioning? Have you tried setting the BindByName property like Silvia mentioned? I’m curious - does this happen with all Oracle versions or just specific ones? Also wondering if numbered parameters like :1, :2 instead of named ones would work around this.

3

Oracle’s default parameter binding uses positional matching, not parameter names. When you change the order of adding parameters, Oracle maps them wrong to your SQL placeholders. I hit this exact issue during a database migration. Set myCommand.BindByName = true before adding parameters - it’ll fix the problem completely. This forces Oracle to match parameters by their actual names instead of position. Without it, Oracle ignores parameter names and binds purely by the sequence they were added. That’s why your first version works and the second doesn’t.

4

yep, oracle parameters r positional, unlike sql server. you can use OracleParameter with ParameterDirection or set BindByName = true on the command obj. that should fix the order issue with your query.