ORA-01036: illegal variable name/number

Asked

Viewed 8,406 times

1

I’m having a little problem in my application. The routine of INSERT causes me to make a mistake:

ORA-01036: illegal variable name/number

Remembering that the database is Oracle and the application in C#. Follows code that generates the error:

public void SalvarCampos(ModelCampos model)
{
    connectionBanco.ConectarBanco(modelLogin);

    String query =  "INSERT INTO CARTEIRA_CREDITO (ANOMES, ANOMESBASE1, ANOMESBASE2, ANOMESBASE3, COD_COOP, ATIVO, CENTRALIZACAO, VLR_SUBTOTAL, VLR_CARTEIRACREDITO) " +
                        "VALUES (iANOMES, iANOMESBASE1, iANOMESBASE2, iANOMESBASE3, iCOD_COOP, iATIVO, iCENTRALIZACAO, iVLR_SUBTOTAL, iVLR_CARTEIRACREDITO)";


        OracleCommand command = new OracleCommand(query, connectionRateio.connection);
        command.CommandType = CommandType.Text;
        command.Parameters.AddWithValue("iANOMES", Convert.ToInt32(model.anoMes));
        command.Parameters.AddWithValue("iANOMESBASE1", Convert.ToInt32(model.anoMesBase1));
        command.Parameters.AddWithValue("iANOMESBASE2",Convert.ToInt32(model.anoMesBase2));
        command.Parameters.AddWithValue("iANOMESBASE3", Convert.ToInt32(model.anoMesBase3));
        command.Parameters.AddWithValue("iCODCOOP", model.codCoop);
        command.Parameters.AddWithValue("iATIVO", model.ativo);
        command.Parameters.AddWithValue("iCENTRALIZACAO", model.centralizacao);
        command.Parameters.AddWithValue("iVLRSUBTOTAL", model.vlrSubTotal);
        command.Parameters.AddWithValue("iVLRCARTEIRACREDITO", model.vlrCarteiraCredito);

    try
    {
        command.ExecuteNonQuery();// <-- ERRO DISPARADO NESSA LINHA!
        command.Transaction.Commit();
        connectionRateio.FecharConexaoBanco();
    }
    catch (Exception exc)
    {
        //Erro
    }

    connectionBanco.FecharConexaoBanco();
}

Follows the field types in the application:

  • ANOMES, ANOMESBASE1, ANOMESBASE2, ANOMESBASE3, COD_COOP: NUMBER
  • ACTIVE, CENTRALIZED, VLR_SUBTOTAL, VLR_CARTEIRACREDITO: NUMBER(15,2)

Can anyone explain to me the reason for this mistake?

c# oracle
  • 1

    Update your question with the types of each of the fields of the port_credito (field1,field2...) and the values that are passed to iCampo1, iCampo2...your error is probably of incompatible field/value types

  • Do the variables in the Oracle statements have no prefix required? I have seen JDBC documentation that uses :iCAMPO1 instead of just CAMPO1 (in query variable only, Addwithvalue equals).

  • Updated @Dante!

  • @luiscubal, I also read about it. I’ve used the prefixes ':' and '@', but it didn’t help.

  • 1

    Already answered here below! Check it out! D

3 answers

2

The first column represents the names in insert and the second represents the names in command.Parameters (where you feed each field of the Insert):

1) iANOMES ----------------> iANOMES  
2) iANOMESBASE1 -----------> iANOMESBASE1
3) iANOMESBASE2 -----------> iANOMESBASE2
4) iANOMESBASE3 -----------> iANOMESBASE3
5) iCOD_COOP --------------> iCODCOOP
6) iATIVO -----------------> iATIVO
7) iCENTRALIZACAO ---------> iCENTRALIZACAO
8) iVLR_SUBTOTAL ----------> iVLRSUBTOTAL
9) iVLR_CARTEIRACREDITO ---> iVLRCARTEIRACREDITO

Have you not noticed anything wrong, more specifically on lines 5, 8 and 9? Shouldn’t the names be identical? I believe this is the problem.

Also check that the addition of : in front of each parameter of the insert.

  • Thank you Electus! Thank you Electus!

2


I believe your mistake lies in the parameters you passed. This error happens mainly when the parameters passed are not the same as those in the query and you can see that your last 2 fields are different from the ones passed in Value.

command.Parameters.AddWithValue("iVLRSUBTOTAL", model.vlrSubTotal);
command.Parameters.AddWithValue("iVLRCARTEIRACREDITO", model.vlrCarteiraCredito);


"VALUES (iANOMES, iANOMESBASE1, iANOMESBASE2, iANOMESBASE3, iCOD_COOP, iATIVO, iCENTRALIZACAO, iVLR_SUBTOTAL, iVLR_CARTEIRACREDITO)";

Remove "_" (Underlines) from the parameters and test again.

  • The command.Parameters.Addwithvalue("iCODCOOP", model.codCoop); also!

  • Thank you Dante! Thank you! Negative point for me.

  • 1

    Ki nothing, man! Happens to all rs Success to you!

0

I was with the same problem in an application that I was giving maintenance, among all the adjustments to prepare the environment, also I was almost an hour bumping head with this error "ORA-01036: illegal variable name/number" until it discovered that the connection of the application was pointed to an outdated database, and thus the application passed two more parameters to the outdated database database database database.

Browser other questions tagged c# oracle

You are not signed in. Login or sign up in order to post.