ORA-01400 无法插入空值...但我没有插入空值！

发布于 2024-10-22 05:23:14 字数 1613 浏览 5 评论 0原文

我尝试使用 C# 应用程序中的 ODP.NET 在 Oracle 表中插入数据，但对于我不在其中的列，出现 ORA-01400 无法插入空值 错误插入空值。

这是我尝试执行的参数化 SQL 命令的精简版本。它包装在 OracleCommand 中，并通过调用 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;

该行以前不存在，因此它是命令的 insert 部分被执行的那个。当然，我已经验证了所有列名和列值参数都正确放置在 SQL 文本中。

问题出在 VisitaLaboral 列中。它的类型为 NUMBER(1,0)，它不接受 NULL，并且我尝试插入值 0。这是 Visual Studio 在命令执行之前立即显示的有关关联 OracleParameter 的内容：

在此处输入图像描述

但是，如果我直接在 Application Express 中执行命令（直接在命令文本中提供值），它工作正常并且该行已插入。

那么，这里发生了什么？ ODP.NET 库中是否存在错误，或者我做错了什么？

附加信息：

数据库是 Oracle 10g Express Release 10.2.0.1.0
Oracle.DataAccess.dll 版本是 4.112.1.2
使用 Visual Studio 2010，针对 .NET Framework 4.0

预先感谢您！

更新：

如果我使用（已弃用的）System.Data.OracleClient 类而不是 ODP.NET，则一切正常。

WTF，甲骨文？不，真的，WTF？

原文

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: