ORA-01400 can't insert null value... but I am NOT inserting null value!

Question

I am trying to insert data in an Oracle table by using ODP.NET from a C# application, but I am getting an ORA-01400 can't insert null value error for a column in which I am NOT inserting a null value.

This is the stripped down version of the parametrized SQL command I am trying to execute. It is wrapped in an OracleCommand and executed with an invokation of ExecuteNonQuery:

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 row does not exist previously so it is the insert part of the command the one that is executed. Of course I have verified that all the column names and column value parameters are properly placed in the SQL text.

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

However if I execute the command directly in Application Express (providing the values directly in the command text), it works fine and the row is inserted.

So, what is happening here? Is there a bug 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 .NET framework 4.0

Thank you in advance!

UPDATE:

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

WTF, Oracle? No, really, WTF?

Answer 1

Your problem seems to be that you don't know what is actually happening in the database. The quickest solution will be to find out what happens and use that.

1. connect to the database
2. use dbms_session.set_sql_trace(true) to enable an sql trace
3. do your application action
4. disconnect from the database
5. find - or ask your dba - to send you the raw trace file

In the tracefile (a plain text file) find your code and see what happens when the error is raised.

It could be that a trigger fired ....