oracle ORA-01400 无法插入空值...但我没有插入空值！
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5323359/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01400 can't insert null value... but I am NOT inserting null value!
提问by Konamiman
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 valueerror for a column in which I am NOT inserting a null value.
我正在尝试使用 C# 应用程序中的 ODP.NET 在 Oracle 表中插入数据，但我收到一个ORA-01400 无法为我没有插入空值的列插入空值错误。
This is the stripped down version of the parametrized SQL command I am trying to execute. It is wrapped in an OracleCommandand executed with an invokation of ExecuteNonQuery:
这是我试图执行的参数化 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;
The row does not exist previously so it is the insertpart 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.
该行以前不存在，因此它insert是执行的命令的一部分。当然，我已经验证了所有列名和列值参数都正确放置在 SQL 文本中。
The problem is in the VisitaLaboralcolumn. 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 OracleParameterimmediately before the command execution:
问题出在VisitaLaboral列中。它的类型为 NUMBER(1,0)，它不接受 NULL，我试图插入一个 0 值。这是 Visual StudioOracleParameter在命令执行前立即显示的关于关联的内容：
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.
但是，如果我直接在 Application Express 中执行命令（直接在命令文本中提供值），它工作正常并插入行。
So, what is happening here? Is there a bug in the ODP.NET library, or am I doing something wrong?
那么，这里发生了什么？ODP.NET 库中是否存在错误，或者我做错了什么？
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
- 数据库是 Oracle 10g Express Release 10.2.0.1.0
- Oracle.DataAccess.dll 版本为 4.112.1.2
- 使用 Visual Studio 2010，面向 .NET framework 4.0
Thank you in advance!
先感谢您！
UPDATE:
更新：
Everything works fine if I use the (deprecated) System.Data.OracleClientclasses instead of ODP.NET.
如果我使用（不推荐使用的）System.Data.OracleClient类而不是 ODP.NET，一切正常。
WTF, Oracle? No, really, WTF?
跆拳道，甲骨文？不，真的，WTF？
回答by ik_zelf
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.
您的问题似乎是您不知道数据库中实际发生了什么。最快的解决方案是找出发生了什么并使用它。
- connect to the database
- use dbms_session.set_sql_trace(true) to enable an sql trace
- do your application action
- disconnect from the database
- find - or ask your dba - to send you the raw trace file
- 连接到数据库
- 使用 dbms_session.set_sql_trace(true) 启用 sql 跟踪
- 做你的申请行动
- 与数据库断开连接
- 查找 - 或询问您的 dba - 向您发送原始跟踪文件
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 ....
可能是触发器触发了......
回答by HamoriZ
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?
这是一条 DB 级别的错误消息，您可以确定插入具有空值。是否可以注销ODP.NET生成的sql语句？
回答by pablo
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)?
好吧，我对 ODP.net 一无所知，但是如果参数中有一个值，那么 precison、scale 和 size 属性是否都应该为零（就像它们看起来一样）？
回答by swissben
We had the same problem. We solved the problem setting column property "IsNullable" to true.
我们遇到了同样的问题。我们解决了将列属性“IsNullable”设置为 true 的问题。
回答by Stefan Steinegger
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" ...
您真的确定插入子句和值子句中的列顺序相同吗？检查你的“一堆列”......
