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

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

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

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

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

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

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

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

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

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

Examples related to c#

Examples related to oracle