5

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:

enter image description here

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Konamiman
  • 49,681
  • 17
  • 108
  • 138
  • I don't know but maybe [this question](http://stackoverflow.com/questions/4863960/could-somebody-explain-what-the-merge-statement-really-does-in-oracle) could interest you. – Benoit Mar 16 '11 at 09:47
  • did you try the "command.bindbyname = True" in ODP, (System.Data.OracleClient binds by name by default whereas ODP binds by position by default). Try the bindbyname=true and see what happens – Harrison Mar 16 '11 at 12:52
  • @Harrison: Yes, saw that in another SO question and tried it, but did not work either. – Konamiman Mar 16 '11 at 15:32
  • 7 years later and still experiencing something similar... I put if statements to prevent null insertion on my data context, but still somehow manages to try to insert a null.... – gitsitgo Mar 20 '18 at 16:26

5 Answers5

2

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

1

This is a DB level error message, you can be sure that the insert has null value. Is it possible to log out the sql statement generated by the ODP.NET?

HamoriZ
  • 2,370
  • 18
  • 38
  • Is is a OracleCommand.ExecuteNonQuery invokation, so unless I am missing something, ODP.NET does not generate any SQL statement, as I am directly providing it. Anyway how could I configure such logging? – Konamiman Mar 16 '11 at 10:04
0

Are you really really sure that you have the columns in the same order on both the insert clause and the values clause? Check your "bunch of columns" ...

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
  • That's the first thing I double-checked. Everything seems to be in the correct place. – Konamiman Mar 16 '11 at 10:02
  • @konaniman, I agree with @Stefan here, I would recommend setting the bindbyname on your command object to true [command.bindbyname = True], make sure the names in the command parameter match the anonymous block, then you will not need to worry about order. These problems usually are related to param order issues. – Harrison Mar 16 '11 at 11:58
0

Well I know nothing about ODP.net, but if there is a value in the parameter, should the precison, scale and size attributes all be zero (as they appear to be)?

pablo
  • 408
  • 2
  • 4
0

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

swissben
  • 1,059
  • 8
  • 13