I'm curious if this is a bug in the Oracle ODP.NET provider. I created a parameterized insert statement. I named one of the parameters ':EMPNO' and when testing it gave it a value of '8000'. In the database the EMPNO column was defined as varchar2(4 byte). However, the insert gave an error message of
ORA-12899: value too large for column "HR"."HR_DEPARTURE"."EMPNO" (actual: 6, maximum: 4)
Here is some code snipets:
"INSERT INTO HR.HR_DEPARTURE (EMPNO) ':EMPNO'"
I then add a parameter
new OracleParameter(":EMPNO", OracleDbType.Varchar2) {Value = empNo ?? Convert.DBNull}
Create a command and add the parameter (there were multiple parameters thus the array)
DbCommand cmd = Connection.CreateCommand();
cmd.Parameters.AddRange(sqlParams.ToArray());
I did some research and considered things like encoding and the fact that Oracle defaults to bind by position (instead of BindByName). However, none of these resolved the issue. I then took a shot in the dark and changed the parameter name to ":EMPN" and got the following error message:
ORA-12899: value too large for column "HR"."HR_DEPARTURE"."EMPNO" (actual: 5, maximum: 4)
This clued me in to change the parameter name to ":EMP" at which time the query worked. I find it very odd that the provider is enforcing the database column size on the parameter name in c#. The database size should be an enforcement of the value ('8000' which I kept the same in all tests).
No; the problem is that your query makes no sense.
':EMPNO' is a string with the literal value :EMPNO, which is five characters long.
To reference a parameter, don't write a string literal.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments