我正在尝试创建一个测试应用程序,该应用程序将向Oracle存储过程传递一个对象数组(在我的示例中为Employees)。
当我调用ExecuteReader
来调用该过程时,收到错误
列包含NULL数据
但我不知道那是什么意思。
下面是我使用的代码。我有以下类:
[OracleCustomTypeMapping("EMTEST")]
public class Employee : IOracleCustomType, INullable
{
public Employee(int? Id, string Name)
{
this.Id = Id;
this.Name = Name;
}
[OracleObjectMapping("Name")]
public string Name { get; set; }
[OracleObjectMapping("Id")]
public int? Id { get; set; }
public bool IsNull { get; set; }
public static Employee Null => new Employee(0, "") { IsNull = true };
public void FromCustomObject(OracleConnection con, object udt)
{
OracleUdt.SetValue(con, udt, "Id", Id);
OracleUdt.SetValue(con, udt, "Name", Name);
}
public void ToCustomObject(OracleConnection con, object udt)
{
Id = ((int?)(OracleUdt.GetValue(con, udt, "Id")));
Name = ((string)(OracleUdt.GetValue(con, udt, "Name")));
}
}
在oracle端,我创建一个类型:
create or replace TYPE EMTEST AS OBJECT
(
Id number null,
Name varchar2(50) null
);
以及一个名为PKS_CARAT的程序包中的过程
这是标题(在其中定义类型的表格):
type T_EMTEST_ARRAY is table of EMTEST;
procedure TestGetObjectsBack(
Employes in T_EMTEST_ARRAY,
ResultTest OUT RC_Result
);
这是身体:
procedure TestGetObjectsBack(
Employes in T_EMTEST_ARRAY,
ResultTest OUT RC_Result
)
is
begin
OPEN ResultTest FOR
select 1 as Id,'mika' as Name from dual;
-- select e.Id,e.Name from table (Employes) e;
end TestGetObjectsBack;
以下是调用存储过程的代码:
var oracleConnection = new OracleConnection("User Id=xxx;Password=xxx;Data Source=ORAKISDEV2;Connection Lifetime=10;Min Pool Size=1;Max Pool Size=200;");
var employeList = new List<Employee>()
{
new Employee(1,"Zoki"),
new Employee(2, "Pera"),
};
var employeArray = employeList.ToArray();
var employeParam = new OracleParameter("Employes", OracleDbType.Object, ParameterDirection.Input);
employeParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
employeParam.Size = employeArray.Length;
employeParam.UdtTypeName = "PKS_CARAT.T_EMTEST_ARRAY";
employeParam.Value = employeArray;
var cursor = new OracleParameter("ResultTest", OracleDbType.RefCursor, ParameterDirection.Output);
oracleConnection.Open();
using (var oracleCommand = oracleConnection.CreateCommand())
{
oracleCommand.Connection = oracleConnection;
oracleCommand.CommandText = "PKS_CARAT.TestGetObjectsBack";
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.BindByName = true;
oracleCommand.Parameters.Add(employeParam);
oracleCommand.Parameters.Add(cursor);
var reader = oracleCommand.ExecuteReader();
....
我读了很多文章,最重要的有:
- pass array of objects to oracle stored procedure using ODP.Net managed client
- Why do I get "Invalid Parameter binding" when using Oracle stored procedure with Udt and C#
我尝试了许多建议,但没有任何不同。我仍然得到列包含空数据,没有任何解释哪个数据为空或为什么会导致此问题。我检查了类中的列和oracle类型中的列是否相同,看起来它们是相同的。
完整的例外文本为:* “列包含NULL数据”。来自“Oracle. ManagedDataAccess”*
异常的类型为系统.InvalidCastException
我正在使用nuget软件包Oracle.管理数据访问.核心版本3.21.80
下面是异常的堆栈顶部跟踪:
OracleInternal.UDT.Types.UDTNamedType.GetNamedTypeMetaData(OracleConnection conn, OracleCommand getTypeCmd)
at OracleInternal.UDT.Types.UDTTypeCache.GetMetaData(OracleConnection conn, UDTNamedType udtType)
at OracleInternal.UDT.Types.UDTTypeCache.CreateUDTType(OracleConnection conn, String schemaName, String typeName)
at OracleInternal.UDT.Types.UDTTypeCache.GetUDTType(OracleConnection conn, String schemaName, String typeName)
at OracleInternal.ConnectionPool.OraclePoolManager.GetUDTType(OracleConnection conn, String schemaName, String typeName)
at Oracle.ManagedDataAccess.Client.OracleConnection.GetUDTTypeFromCache(String schemaName, String typeName)
at Oracle.ManagedDataAccess.Client.OracleParameter.GetUDTType(OracleConnection conn)
at Oracle.ManagedDataAccess.Client.OracleParameter.PreBind_UDT(OracleConnection conn)
at Oracle.ManagedDataAccess.Client.OracleParameter.PreBind(OracleConnectionImpl connImpl, ColumnDescribeInfo cachedParamMetadata, Boolean& bMetadataModified, Int32 arrayBindCount, ColumnDescribeInfo& paramMetaData, Object& paramValue, Boolean isEFSelectStatement, SqlStatementType stmtType)
at OracleInternal.ServiceObjects.OracleCommandImpl.InitializeParamInfo(ICollection paramColl, OracleConnectionImpl connectionImpl, ColumnDescribeInfo[] cachedParamMetadata, Boolean& bMetadataModified, Boolean isEFSelectStatement, MarshalBindParameterValueHelper& marshalBindValuesHelper)
at OracleInternal.ServiceObjects.OracleCommandImpl.ProcessParameters(OracleParameterCollection paramColl, OracleConnectionImpl connectionImpl, ColumnDescribeInfo[] cachedParamMetadata, Boolean& bBindMetadataModified, Boolean isEFSelectStatement, MarshalBindParameterValueHelper& marshalBindValuesHelper)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, Int64 internalInitialJSONFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()
at OracleArrayTest.Program.TestCollectionCustom() in C:\project\BulkProject\OracleArrayTest\Program.cs:line 203
有人知道我做错了什么吗?这个异常告诉我什么?
1条答案
按热度按时间z9gpfhce1#
我发现这篇文章:
有人在评论中给出了一个链接,指向如何将自定义对象数组发送到oracle的示例(对我来说最好的示例是Nested-Table.cs):
在阅读了注解和示例后,我做了如下修改:
CREATE OR REPLACE TYPE "T_EMTEST_ARRAY" AS TABLE OF EMTEST;
在那之后,它起作用了。困难的部分是找到正确的线程。你可以在这里阅读更多关于甲骨文为. net核心引入UDT的信息: