ORA-01036: invalid variable name / number when starting a request through C #

Question

I am trying to use a query ALTER USERfor an Oracle database using OracleCommand in C # in the following code. It creates a query if the values ​​for the username and password are not blank lines. But when ExecuteNonQuery()I execute, I get an error message "ORA-01036: illegal variable name/number".

  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 suggest what is wrong with my implementation?

Answer 1

The main reason

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

• (DDL). . "ALTER" "CREATE"
• Langugage (DML). , . "INSERT", "MERGE" "DELETE".
• , " " ( , , ). "SELECT".

Bind Oracle DML . , . .

. , .

, DBMS_ASSERT.

, Oracle , . DDL . , .

DML , ( ), .. . (, ).

Answer 2

, , 60 . , ( Oracle Write, ) . , , , . 4 , , , .net Oracle. , - . .,

Answer 3

oracleCommand, 'for'. , OracleCommand. cmd.Parameters.clear() .

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();                   


            }
        } 

Answer 4

Oracle erro ORA-01036 , undefined. , : , @. , advacned , , , , Oracle Case Sensitive.

Answer 5

, , , , . , .

Answer 6

/ pl/sql . sql .

Answer 7

, cmd.CommandType = System.Data.CommandType.StoredProcedure;

Answer 8

 cmd.Parameters.Add(new OracleParameter("GUSERID ", OracleType.VarChar)).Value = userId;

, , "GUSERID". , .

Answer 9

I had the same problem in the application that I supported, among all the settings for preparing the environment, I also spent almost an hour banging my head with this error "ORA-01036: invalid variable name / number" until I found that the application connection was pointed to an outdated database, so the application passed two more parameters to the outdated database procedure, which caused an error.