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

score 25 · Answer 1 · edited Nov 02 '16 at 19:26

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

score 23 · Answer 2 · answered Dec 27 '18 at 21:41

23

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.

answered Dec 27 '18 at 21:41

Beatscribe

423
8
17

2

fixed it for me. Lost 2 days trying to solve this one. – osullic Nov 30 '20 at 18:55
1

This solved my problem also using Python + cx_Oracle, really a time saver, was about to do my query step by step. Once again thanks Oracle for your useless error messages. – Neto Yo Nov 25 '22 at 18:38

score 14 · Answer 3 · answered May 12 '16 at 19:33

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

Thanks. I considered this and carefully watched all my parameters. Found one that was `AddIntParameter(cmd, ":ASSIST_FLG ", obj.Assist);`.... — Marcel, Feb 22 '17 at 15:58

score 6 · Answer 4 · edited Mar 30 '16 at 11:55

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<usersList.Count;i++)
        {
            if (!(selectedUsersArray[i].Equals("")) && !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();                   


            }
        }

This solved my problem. Thank you. Heaven forbid the Oracle driver actually replace the existing parameter value each time it is called, rather than throw a vague catch-all exception. — Steven, Jul 27 '18 at 00:32

score 3 · Answer 5 · edited Jun 02 '20 at 11:26

3

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.

edited Jun 02 '20 at 11:26

Superdooperhero

7,584
19
83
138

answered Jan 27 '14 at 07:39

Ravi Mavani

61
3

variables start with `:` in Oracle syntax. – Nyerguds Mar 02 '21 at 09:25

score 2 · Answer 6 · answered Oct 06 '16 at 15:55

2

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

answered Oct 06 '16 at 15:55

mpora

1,411
5
24
65

score 2 · Answer 7 · answered Sep 25 '18 at 03:02

2

 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 .

answered Sep 25 '18 at 03:02

Sribin

307
4
16

score 0 · Answer 8 · edited Jun 02 '20 at 11:27

0

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.

edited Jun 02 '20 at 11:27

Superdooperhero

7,584
19
83
138

answered Jan 27 '14 at 07:39

hkutluay

6,794
2
33
53

score 0 · Answer 9 · answered Sep 18 '19 at 14:12

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.

score 0 · Answer 10 · answered Oct 19 '20 at 06:49

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

score 0 · Answer 11 · answered Apr 26 '21 at 08:14

I had the same issue today when using Python module cx_Oracle. In my case, the root cause was an invalid variable name.

Example: SELECT * FROM DATA WHERE KEY IN (:_0, :_1, ...)

When I changed _0 to var0, it worked fine.

From this blog post, I found these rules for variable names:

Must start with a letter
Maximum size is limited to 30 letters
Cannot contain whitespace characters
Can contain dollar sign ('$'), underscore ('_') and hash sign ('#')
Is case-insensitive

Sherlock-Holmes-2-2-1 · Answer 12 · 2022-06-02T10:06:09.153

I had the same problem, was learning connection to oracledb. previous code-

SELECTALLCANDIDATES = "Select * from candidate_master";
data= await connection.execute(SELECTALLCANDIDATES, {autoCommit:true})

Removed the {autoCommit:true} and it started working fine.
Correct code-

SELECTALLCANDIDATES = "Select * from candidate_master";
data= await connection.execute(SELECTALLCANDIDATES)

Still don't know why but it works.

score 0 · Answer 13 · answered Apr 26 '23 at 15:59

Just wanted to add to the points already mentioned by @stefan.schwetschke. At the moment, Oracle does not support executemany() with "select" queries.

Example of a code snippet which gives the error is :

sql = """
    SELECT *
    FROM TABLE1
    WHERE 
        col1 = :cd
"""
data = [{'cd' : 1}, {'cd': 2}, {'cd': 3}]

cur.executemany(sql, data)

score -1 · Answer 14 · answered Mar 11 '22 at 13:16

-1

Check your named variables match, I had the same problem, I had a spelling mistake/typo in one of my parameters

answered Mar 11 '22 at 13:16

Chuma Madyosi

1
1

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

14 Answers14

The root cause

Solution

Background

Linked

Related