1

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).

user3245753
  • 13
  • 2
  • In 40+ years of writing software I've discovered two actual verified bugs in vendor-supplied software. I've thought I've discovered tons of them. This has led me to an observation: any time I believe I've found a bug it's extremely likely that the "bug" is located in my code, and that checking my own code over with a very fine comb is probably more productive than blaming the vendor (whose testing criteria likely are better than mine). Just an observation... – Bob Jarvis - Слава Україні May 13 '15 at 22:31
  • Thanks for your observation Bob. I also agree that it is more likely for a single developer to have a bug in their implementation than the vendor. However I do not think asking "Is this a bug" is the same thing as "blaming the vendor". – user3245753 May 14 '15 at 15:40

1 Answers1

5

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.

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
  • Re-reading my question I see I created a cut and paste error in asking it. This isn't the question I wanted to ask, but your response is of course correct to the question I did ask. – user3245753 May 14 '15 at 15:31