Oracle Insert query

Question

I am getting this error "ORA-01722: illegal/invalid number"

            decimal first2var = Convert.ToDecimal(var1);
            decimal id_refeicao;

            //...
            // save values in var insted of lables
            decimal id_usuario = (decimal)dr.GetInt32(0);
            //...
            var idUsuarioParameter = new OracleParameter
            {
                ParameterName = "id_usuario",
                OracleDbType = OracleDbType.Decimal,
                Direction = ParameterDirection.Input,
                Value = id_usuario
            };
            var idRefeicaoParameter = new OracleParameter
            {
                ParameterName = "id_refeicao",
                OracleDbType = OracleDbType.Decimal,
                Direction = ParameterDirection.Input,
                Value = id_refeicao
            };
            var var1Parameter = new OracleParameter
            {
                ParameterName = "first2var",
                OracleDbType = OracleDbType.Decimal, //-- populate with correct oracle type
                Direction = ParameterDirection.Input,
                Value = first2var
            };
                OracleConnection connection = new OracleConnection(oradb);
                connection.Open();
                cmd.Connection = connection;
                cmd.CommandText = "INSERT INTO SER_REFEICAO_USUARIO (USUARIO, REFEICAO, DATA_HORA, ORIGEM ) VALUES(:id_usuario, :id_refeicao, SYSDATE , :first2var )";
                cmd.Parameters.Add(idUsuarioParameter);
                cmd.Parameters.Add(idRefeicaoParameter);
                cmd.Parameters.Add(var1Parameter);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                connection.Close();

Can anyone help me what i am doing wrong here, if i hard code my values in my insert query, it works fine but if i pass the parameters as above so i got the error.

Why do you have var2 in your command tex, but add var1 as a parameter? Also, are the ids numeric or strings? — Daniel Kelley
– Daniel Kelley, Commented Jun 6, 2014 at 18:48
@DanielKelley ids are numeric but i defined there data type string, like this "string id_usuario" — user2956373
– user2956373, Commented Jun 6, 2014 at 20:00
What about the definition of table SER_REFEICAO_USUARIO, can you tell us what type each of the columns is? — Sven Grosen
– Sven Grosen, Commented Jun 9, 2014 at 15:29
Well thanks. i found the problem and the problem is, I am not defining the OracleCommand() so it was taking the old values and using the old values in the query. — user2956373
– user2956373, Commented Jun 9, 2014 at 16:54

Sven Grosen · Accepted Answer · 2014-06-06 18:52:59Z

1

According to this source, you should omit the : from your parameter names, so it should be like this:

cmd.Parameters.Add("id_usuario", id_usuario);
cmd.Parameters.Add("id_refeicao", id_refeicao);
cmd.Parameters.Add("var1", var1);

Also, as mentioned in the comments, you have a mismatch between your query text:

INSERT INTO SER_REFEICAO_USUARIO (USUARIO, REFEICAO,    DATA_HORA,  ORIGEM)
 VALUES(:id_usuario, :id_refeicao, SYSDATE, :var2 )

And your parameter declarations:

cmd.Parameters.Add("var1", var1);

edited Jun 6, 2014 at 18:52

answered Jun 6, 2014 at 18:47

Sven Grosen

5,6363 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sven Grosen Over a year ago

@alykins, here's a starting point: owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

user2956373 Over a year ago

@SvenGrosen here is my query with hard coded values "INSERT INTO SER_REFEICAO_USUARIO (USUARIO, REFEICAO,ORIGEM, DATA_HORA) VALUES(139773, 68, SYSDATE , 02 )

Sven Grosen Over a year ago

@user2956373 Then what happens if you don't do a ToString() and define id_usuario and id_refeicao as ints?

user2956373 Over a year ago

@SvenGrosen i just tried with out ToString() but same error illegal variable number/name

user2956373 Over a year ago

@SvenGrosen i have updated my question with my whole code, can you give me something what i am doing wrong ?

mhand · Accepted Answer · 2014-06-09 11:57:31Z

0

I'm assuming you're using ODP.NET. In my experience, I always have to specify the OracleDbType.Decimal because for some reason Oracle doesn't like byte, int, or long. Another issue is we're not passing the OracleDbType along with the parameter, so ODP.NET is seeing that you passed in a string and is probably saying the parameter is of type string and not of the type you want. This coupled with Sven Grosen's answer should do it for you. you end up with something like below:

decimal id_usuario = (decimal)dr.GetInt32(0);
decimal id_refeicao = (decimal)dtre.GetInt32(0);
var idUsuarioParameter = new OracleParameter
{
    ParameterName = "id_usuario",
    OracelDbType = OracleDbType.Decimal,
    Direction = ParameterDirection.Input,
    Value = id_usuario
};
var idRefeicaoParameter = new OracleParameter
{
    ParameterName = "id_refeicao",
    OracelDbType = OracleDbType.Decimal,
    Direction = ParameterDirection.Input,
    Value = id_refeicao 
};    
var var1Parameter = new OracleParameter
{
    ParameterName = "var1",
    //OracelDbType = OracleDbType.Decimal, -- populate with correct oracle type
    Direction = ParameterDirection.Input,
    Value = var1 
};


connection.Open();
cmd.Connection = connection;
cmd.CommandText = "INSERT INTO SER_REFEICAO_USUARIO (USUARIO, REFEICAO,    DATA_HORA,  ORIGEM) VALUES(:id_usuario, :id_refeicao, SYSDATE, :var1 )";
cmd.Parameters.Add(idUsuarioParameter);
cmd.Parameters.Add(idRefeicaoParameter);
cmd.Parameters.Add(var1Parameter);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
connection.Close();

I think that should do it for you. NOTE: untested/uncompiled code.

edited Jun 9, 2014 at 11:57

answered Jun 6, 2014 at 18:56

mhand

1,3071 gold badge12 silver badges23 bronze badges

11 Comments

user2956373 Over a year ago

i tried the way you mentioned above but still i am getting error "ORA-01036 illegal variable name/number" ..

user2956373 Over a year ago

another thing i am not able to put OracleDbType.Decimal, it is not accepting this at this line

mhand Over a year ago

I've updated the code to explicitly create the OracleParameter types before adding them to the cmd.Parameters collection. The important part here is we are telling Oracle what type we're using.

user2956373 Over a year ago

can you give me you id. i would like to discuss whole code and its logic with you.. Thanks

mhand Over a year ago

SO doesn't provide a means for user to user messaging, we can discuss here or you can open a new question and link it here, and I can respond. This way others who find this post will have the answers to your questions as well.

|

Collectives™ on Stack Overflow

Oracle Insert query

2 Answers 2

5 Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related