Error: 'ORA-01438: value larger than specified precision allowed for this column' faultactor: 'null' detail: org.kxml2.kdom.Node@99c0b9c

Question

I'm trying to update oracle table records using c#. I'm getting this error when i execute my code SoapFault - faultcode: 'a:InternalServiceFault' faultstring: 'ORA-01438: value larger than specified precision allowed for this column' faultactor: 'null' detail: org.kxml2.kdom.Node@99c0b9c.

This is the code that I'm using in my service side:

[OperationContract]

public int pushData(string CustomObjects)
{

  List<CustomObject> myDeserializedObjList = (List<CustomObject>)Newtonsoft.Json.JsonConvert.DeserializeObject(CustomObjects, 
  typeof(List<CustomObject>));
  string constr = "my connection string";
  int rowid = 0;
  using(OracleConnection con = new OracleConnection(constr)){

  con.Open();
  OracleCommand cmd = new OracleCommand();
  cmd.Connection = con;
  cmd.CommandType = CommandType.Text;

   foreach (CustomObject element in myDeserializedObjList)
   {

      int num = element.num;

      string mydate = element.mydate;

      long num2 = element.num2;

      string user = element.user;


      string sqlStatement= "UPDATE CustomObjectS SET  num = :num, 
      mydate=:to_date(:mydate, 'YYYY/MM/DD HH:MI:SS'), num2=:num2, user=:user  WHERE num =:num";
      OracleTransaction myTrans;

      // Start a local transaction
      myTrans = con.BeginTransaction();
      // Assign transaction object for a pending local transaction
      cmd.Transaction = myTrans;
      cmd.CommandText=sqlStatement;
      cmd.Parameters.Add(new OracleParameter("num", num));
      cmd.Parameters.Add(new OracleParameter("user", user));
      cmd.Parameters.Add(new OracleParameter("num2", num2));
      cmd.Parameters.Add(new OracleParameter("mydate", mydate));
      cmd.Parameters[3].Value = mydate;


              rowid= cmd.ExecuteNonQuery();

              myTrans.Commit();



      }


      }


  return rowid;

      }

Could you please help

Answer 1

ORA-01438: value larger than specified precision allows for this column

This error appears during either an INSERT or an UPDATE statement. It means, that you are trying to assign a value to a column exceeded the precision defined for this column.

A simple example:

CREATE TABLE abc1(
  x NUMBER(5)
);


INSERT INTO abc1( x ) values (123456 );
ORA-01438: value larger than specified precision allowed for this column


INSERT INTO abc1( x ) values (12345 ); 
1 row inserted.

---

What you can do:

• Assign a smaller precision value to the column (don't assing so big numbers).
• Alter the definition of the table to allow for a higher precision number in the column. This can be done with a ALTER TABLE statement.

---

ALTER TABLE abc1 MODIFY x NUMBER(10);
Table ABC1 altered.

INSERT INTO abc1( x ) values (123456 );
1 row inserted.