ORA-01036: illegal variable name/number when running query through C#

Question

I am trying to use ALTER USER query for Oracle database using OracleCommand in C# in the following code. It creates the query if the values for Username and password are not empty strings. But I get an error "ORA-01036: illegal variable name/number" when ExecuteNonQuery() is executed.

  string updateQuery = "ALTER USER :user IDENTIFIED BY :password";
  connection = new OracleConnection(LoginPage.connectionString);
  connection.Open();                
  OracleCommand cmd = new OracleCommand(updateQuery, connection);
  cmd.Connection = connection;  
  for(int i=0;i<usersList.Count;i++)
        {
            if (!(selectedUsersArray[i].Equals("")) && !passwordArray[i].Equals(""))
            {
                OracleParameter userName = new OracleParameter();
                userName.ParameterName = "user";
                userName.Value = selectedUsersArray[i];

                OracleParameter passwd = new OracleParameter();
                passwd.ParameterName = "password";
                passwd.Value = passwordArray[i];

                cmd.Parameters.Add(userName);
                cmd.Parameters.Add(passwd);

                cmd.Prepare();
                cmd.ExecuteNonQuery();                   


            }
        }

Could you please suggest what is wrong with my implementation?.

Answer 1

The root cause

In Oracle you have three kinds of SQL statements (and additionally there are PL/SQL blocks):

• Statements in the Data Definiton Language (DDL). These statements modify the structure of the database. They begin usually with the verbs "ALTER" or "CREATE"
• Statements in the Data Modification Langugage (DML). There statements modify the content inside of tables, leaving the structure of each table unmodified. These statements usually begin with "INSERT", "MERGE" or "DELETE".
• Statements in what I call "query language" (there seems to be no canonical name for these). This statements start with the verb "SELECT".

Bind variables in Oracle are only allowed in some special places in DML and query statements. You are trying to use bind variables in a places where they are not allowed. Hence the error.

Solution

Build your statement without bind variables. Build the complete query string instead using string concatenation.

If you want to sanitize the input before concatenating the string, use the DBMS_ASSERT package.

Background

Bind variables can only be used when Oracle can build a query plan without knowing the value of the variable. For DDL statements, there is no query plan. Hence bind variables are not allowed.

In DML and query statements, bind variables are only allowed, when they are used inside a tuple (regarding the underlying set theory), i.e. when the value will be compared with the value in a table or when the value will be inserted in a table. They are not allowed to change the structure of the execution plan (e.g. to change the target table or to change the number of comparisons).

Answer 2

Just for others getting this error and looking for info on it, it is also thrown if you happen to pass a binding parameter and then never use it. I couldn't really find that stated clearly anywhere but had to prove it through trial and error.