ORA-01400 cannot insert a null value ... but I DO NOT Insert a null value!

Question

I am trying to insert data into an Oracle table using ODP.NET from a C # application, but I get ORA-01400, I cannot insert a null value error for a column in which I DO NOT insert a null value.

This is a stripped-down version of the parameterized SQL command I'm trying to execute. It is wrapped in OracleCommand and executed with an ExecuteNonQuery call:

 declare c int; begin select count(*) into c from "Entradas" where "Id" = :Id and nvl("AppId", 0) = nvl(:AppId, 0); if c>0 then update "Entradas" set /*...a bunch of columns...*/, "VisitaLaboral" = :VisitaLaboral, /*...some more columns...*/ where "Id" = :Id and nvl("AppId",0) = nvl(:AppId, 0); else insert into "Entradas" ( /*... a bunch of columns...*/, "VisitaLaboral", /*...some more columns...*/ ) values ( /*...a bunch of values...*/, :VisitaLaboral, /*...some more values...*/ ); end if; end; 

The line does not exist before, so this is part of the insert command that is executed. Of course, I checked that all column names and column value parameters are correctly placed in the SQL text.

The problem is the VisitaLaboral column. It is of type NUMBER (1,0), it does not accept NULL, and I am trying to insert a value of 0. This is what Visual Studio displays about the associated OracleParameter just before executing the command:

However, if I execute the command directly in the Express application (providing values ​​directly in the text of the command), it works fine and a line is inserted.

So what is going on here? Is there an error in the ODP.NET library, or am I doing something wrong?

Additional Information:

• Database is Oracle 10g Express Release 10.2.0.1.0
• Oracle.DataAccess.dll version is 4.112.1.2
• Using Visual Studio 2010 targeting the .NET framework 4.0

Thank you in advance!

UPDATE:

Everything works fine if I use the classes (deprecated) of the System.Data.OracleClient instead of ODP.NET.

WTF, Oracle? No, really, WTF?

Answer 1

This is a DB level error message, you can be sure that the insert is null. Is it possible to output the SQL query generated by ODP.NET?

Answer 2

Your problem is that you do not know what is really happening in the database. The quickest solution is to find out what is happening and use.

• connect to database
• use dbms_session.set_sql_trace (true) to enable sql tracing
• complete the action of your application.
• disconnect from the database
• find - or ask your dba - send you a raw trace file

In the trace file (plain text file), find your code and see what happens when an error occurs.

It may happen that the trigger fires ....

Answer 3

Are you really sure that you have the columns in the same order in both the insert clause and the values ​​clause? Check your "column group" ...

Answer 4

Well, I don't know anything about ODP.net, but if there is a value in the parameter, should all precicing, scale, and size attributes be null (as they seem)?

Answer 5

We had the same problem. We fixed the problem by setting the column property "IsNullable" to true.