Oracle error ORA-01722 while updating DECIMAL value

I'm using ODP to update an Oracle 10g DB with no success updating decimal values. Ex:

UPDATE usertable.fiche SET DT_MAJ = '20110627',var = 60.4 WHERE NB = '2143'

Result: 604 in the var column ('.' disappears)

UPDATE usertable.fiche SET DT_MAJ = '20110627',var = 60,4 WHERE NB = '2143'

Result: INVALID NUMBER

UPDATE usertable.fiche SET DT_MAJ = '20110627',var = ‘60,4’ WHERE NB = '2143'

Result: INVALID NUMBER

I als开发者_开发技巧o tried to use TO_NUMBER function without any success. Any idea on the correct format I should use?

Thanks.

You didn't give us much to go on (only the insert statements, not the casting of types or what not)

but here is a test case that shows the how to do it.

create table numTest(numA number(3) ,
                     numB number(10,8) ,
                     numC number(10,2) )
/                     

--test insert
insert into numTest(numA, numB, numC) values (123, 12.1241, 12.12)
/

select * from numTest
/

/*
NUMA                   NUMB                   NUMC                   
---------------------- ---------------------- ---------------------- 
123                    12.1241                12.12  
*/

--delete to start clean
rollback
/
/*by marking these table.col%type we can change the table type and not have to worry about changing these in the future!*/
create or replace procedure odpTestNumberInsert(
                                     numA_in IN numTest.numA%type ,
                                     numB_in IN numTest.numB%type ,
                                     numC_in IN numTest.numC%type)
AS
BEGIN
    insert into numTest(numA, numB, numC) values (numA_in, numB_in, numC_in) ;
END odpTestNumberInsert ;
/



begin
    odpTestNumberInsert(numA_in => 10 
                       ,numB_in => 12.55678
                       ,numC_in => 13.13);
    odpTestNumberInsert(numA_in => 20 
                       ,numB_in => 30.667788
                       ,numC_in => 40.55);                       

end ;

/

select *
  from numTest
/

/*
NUMA                   NUMB                   NUMC                   
---------------------- ---------------------- ---------------------- 
10                     12.55678               13.13                  
20                     30.667788              40.55 
*/

rollback
/

okay, so we have created a table, got data in it (removed it), created a procedure to verify it works (then rollback the changes) and all looks good. So let's go to the .net side (I'll assume C#)

   OracleCommand cmd = new OracleCommand("odpTestNumberInsert", con);
  cmd.CommandType   = CommandType.StoredProcedure;
  cmd.BindByName = true;

  OracleParameter oparam0 = cmd.Parameters.Add("numA_in", OracleDbType.Int64);
  oparam0.Value = 5 ;
  oparam0.Direction = ParameterDirection.Input;

  decimal deciVal = (decimal)55.556677;
  OracleParameter oparam1 = cmd.Parameters.Add("numB_in", OracleDbType.Decimal);
  oparam1.Value = deciVal ;
  oparam1.Direction = ParameterDirection.Input;


  OracleParameter oparam2 = cmd.Parameters.Add("numC_in", OracleDbType.Decimal);
  oparam2.Value = 55.66 ;
  oparam2.Direction = ParameterDirection.Input;

  cmd.ExecuteNonQuery ();

     con.Close();
    con.Dispose();

And then to finish things off:

select *
  from numTest
/

NUMA                   NUMB                   NUMC                   
---------------------- ---------------------- ---------------------- 
5                      55.556677              55.66   

all of our data was inserted.

Without more code on your part I would recommend that you verify that the correct param is being passed in and assoc. to the insert. the above proves it works.

You Should Not re-cast your variables via a TO_NUMBER when you can do so when creating the parameters.

I found the problem just after posting my question !!! I was not looking at the right place... Oracle update was not concerned at all. The problem was in the Decimal.parse method I was using to convert my input string (containing a coma as decimal separator) into the decimal number (with a dot as decimal separatot) I wanted to update in the DB. The thing is that the system culture is not the same on my own development computer than on the client computer, even if they both run in the same country. Then the parse was perfectly working on my computer but was removing the decimal character on the client production environment. I finally just put in place a "replace" coma by dot and everything goes well now. Thanks again for your time.