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 lt usersList Count i                          if    selectedUsersArray i  Equals       amp  amp   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

User · Answer

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).

User · Answer

The Oracle error ORA-01036 means that the query uses an undefined variable somewhere  From the query we can determine which variables are in use  namely all that start with    However  if you re inputting this into an advanced query  it s important to confirm that all variables have a matching input parameter  including the same case as in the variable name  if your Oracle database is Case Sensitive

User · Answer

cmd Parameters Add new OracleParameter  GUSERID    OracleType VarChar   Value   userId    I was having eight parameters and one was with space at the end as shown in the above code for  GUSERID   Removed the space and everything started working

User · Answer

I was having the same problem in an application that I was maintaining  among all the adjustments to prepare the environment  I also spent almost an hour banging my head with this error  ORA-01036  illegal variable name   number  until I found out that the application connection was pointed to an outdated database  so the application passed two more parameters to the outdated database procedure causing the error

User · Answer

I just spent several days checking parameters because I have to pass 60 to a stored procedure   It turns out that the one of the variable names  which I load into a list and pass to the Oracle Write method I created  had a space in the name at the end   When comparing to the variables in the stored procedure they were the same  but in the editor I used to compare them  I didnt notice the extra space   Drove me crazy for the last 4 days trying everything I could find  and changing even the  net Oracle driver   Just wanted to throw that out here so it can help someone else   We tend to concentrate on the characters and ignore the spaces

User · Answer

You cannot pass user table name to pl sql with a parameter  You can create a procedure and build sql and then execute immediately to achieve that

User · Answer

You defined one oracleCommand but used it in  for   it means you are adding parameter with the same name to one OracleCommand  you should use cmd Parameters clear   to refresh your parameters   for int i 0 i lt usersList Count i                          if    selectedUsersArray i  Equals       amp  amp   passwordArray i  Equals                                    cmd Parameters clear     Add this line                 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

User · Answer

I have faced same problem     For the problem is like this  I am calling the PRC inside cpp program and my PRC taking 4 arguments but while calling I used only 1 arguments so this error came for me  Begin Example PRC  1   End     this cause the problem  Begin Example PRC  1  2  3  4   End     this is the solution

User · Answer

This error happens when you are also missing cmd CommandType   System Data CommandType StoredProcedure

User · Answer

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

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

The root cause

Solution

Background

Examples related to c#

Examples related to oracle