Oracle UDTs未指定或无效的自定义类型映射
问题描述:
我收到此错误,“未指定'ClassXX'的自定义类型映射或无效 是否有可能我的成员值类型不正确以匹配数据库?UDT的Oracle UDTs未指定或无效的自定义类型映射
这里是我的代码:
ClassXX
[OracleCustomTypeMapping("DB.T_OUTERTRAN")]
public class ClassXX: AOracleCustomObject<ClassXX>
{
[OracleObjectMapping("OUTERSYSTEMCODE")]
public int OuterSystemCode { get; set; }
[OracleObjectMapping("TRANSACTIONSOURCE")]
public int TransactionSource { get; set; }
[OracleObjectMapping("TRANSACTIONTYPE")]
public int TransactionType { get; set; }
[OracleObjectMapping("AMOUNTCHANGE")]
public decimal AmountChange { get; set; }
[OracleObjectMapping("REFERENCENO")]
public string ReferenceNo { get; set; }
[OracleObjectMapping("REMARKS")]
public string Remarks { get; set; }
[OracleObjectMapping("VENDORID")]
public int VendorId { get; set; }
[OracleObjectMapping("BUID")]
public int BuId { get; set; }
[OracleObjectMapping("USERCODE")]
public string UserCode { get; set; }
[OracleObjectMapping("ACCEPTNEGATIVEBALANCE")]
public int AcceptNegativeBalance { get; set; }
protected override ClassXX _getObjInstance()
{
return this;
}
}
AOracleCustomObject:
public abstract class AOracleCustomObject<T> : INullable, IOracleCustomTypeFactory, IOracleCustomType
{
[IgnoreDataMember]
public bool IsNull
{
get
{
return false;
}
}
public void FromCustomObject(OracleConnection con, IntPtr pUdt)
{
T oracleObj = _getObjInstance();
PropertyInfo[] properties = typeof(T).GetProperties();
foreach (PropertyInfo property in properties)
{
var attrs = (OracleObjectMappingAttribute[])
property.GetCustomAttributes(typeof(OracleObjectMappingAttribute), false);
if (attrs.Length > 0)
{
string attrName = attrs[0].AttributeName;
object value = property.GetValue(oracleObj, null);
OracleUdt.SetValue(con, pUdt, attrName, value);
}
}
}
public void ToCustomObject(OracleConnection con, IntPtr pUdt)
{
T oracleObj = _getObjInstance();
PropertyInfo[] properties = typeof(T).GetProperties();
foreach (PropertyInfo property in properties)
{
var attrs = (OracleObjectMappingAttribute[])
property.GetCustomAttributes(typeof(OracleObjectMappingAttribute), false);
if (attrs.Length > 0)
{
string attrName = attrs[0].AttributeName;
Type ptype = property.PropertyType;
object value = Convert.ChangeType(OracleUdt.GetValue(con, pUdt, attrName), ptype);
property.SetValue(oracleObj, value, null);
}
}
}
public IOracleCustomType CreateObject()
{
return (IOracleCustomType)_getObjInstance();
}
protected abstract T _getObjInstance();
}
的UDT:
create or replace TYPE T_OUTERTRAN AS OBJECT
(
OUTERSYSTEMCODE NUMBER(4,0),
TRANSACTIONSOURCE NUMBER(4,0),
TRANSACTIONTYPE NUMBER(4,0),
AMOUNTCHANGE NUMBER(26,6),
REFERENCENO VARCHAR2(50 BYTE),
REMARKS VARCHAR2(100 BYTE),
VENDORID NUMBER(4,0),
BUID NUMBER(5,0),
USERCODE VARCHAR2(30 BYTE),
ACCEPTNEGATIVEBALANCE NUMBER(1,0)
);
例外日志
Custom type mapping for 'ClassXX' is not specified or is invalid.
System.InvalidOperationException: Custom type mapping for 'ClassXX' is not specified or is invalid.
at Oracle.DataAccess.Types.OracleUdt.GetUdtName(String customTypeName, String dataSource)
at Oracle.DataAccess.Client.OracleParameter.SetUDTFromCustomObject(OracleConnection conn, IOracleCustomType customObj, Int32 i)
at Oracle.DataAccess.Client.OracleParameter.PreBind_Collection(OracleConnection conn)
at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
答
我想简单的往返,一切似乎很好地工作。
void Main()
{
using (var connection = new OracleConnection("DATA SOURCE=HQ_PDB_TCP;PASSWORD=oracle;USER ID=HUSQVIK"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "DECLARE i SYS.ODCICOST := :p; BEGIN :p := SYS.ODCICOST(i.CPUCOST + 1, i.IOCOST + 1, i.NETWORKCOST + 1, i.INDEXCOSTINFO || 'Dummy'); END;";
command.BindByName = true;
var parameter = command.CreateParameter();
parameter.Direction = ParameterDirection.InputOutput;
parameter.ParameterName = "p";
parameter.OracleDbType = OracleDbType.Object;
parameter.UdtTypeName = "SYS.ODCICOST";
parameter.Value = new OdciCost { CPUCOST = 123, INDEXCOSTINFO = "DUMMY", IOCOST = 456, NETWORKCOST = 789 };
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
var odciCost = (OdciCost)parameter.Value;
Console.WriteLine($"CPUCOST={odciCost.CPUCOST}; IOCOST={odciCost.IOCOST}; NETWORKCOST={odciCost.NETWORKCOST}; INDEXCOSTINFO={odciCost.INDEXCOSTINFO}");
}
}
}
[OracleCustomTypeMapping("SYS.ODCICOST")]
public class OdciCost : IOracleCustomType, IOracleCustomTypeFactory, INullable
{
private bool _isNull;
[OracleObjectMapping("CPUCOST")]
public decimal CPUCOST;
[OracleObjectMapping("INDEXCOSTINFO")]
public string INDEXCOSTINFO;
[OracleObjectMapping("IOCOST")]
public decimal IOCOST;
[OracleObjectMapping("NETWORKCOST")]
public decimal NETWORKCOST;
public IOracleCustomType CreateObject()
{
return new OdciCost();
}
public void FromCustomObject(OracleConnection connection, IntPtr pointerUdt)
{
OracleUdt.SetValue(connection, pointerUdt, "CPUCOST", CPUCOST);
OracleUdt.SetValue(connection, pointerUdt, "IOCOST", IOCOST);
OracleUdt.SetValue(connection, pointerUdt, "NETWORKCOST", NETWORKCOST);
OracleUdt.SetValue(connection, pointerUdt, "INDEXCOSTINFO", INDEXCOSTINFO);
}
public void ToCustomObject(OracleConnection connection, IntPtr pointerUdt)
{
CPUCOST = (decimal)OracleUdt.GetValue(connection, pointerUdt, "CPUCOST");
IOCOST = (decimal)OracleUdt.GetValue(connection, pointerUdt, "IOCOST");
NETWORKCOST = (decimal)OracleUdt.GetValue(connection, pointerUdt, "NETWORKCOST");
INDEXCOSTINFO = (string)OracleUdt.GetValue(connection, pointerUdt, "INDEXCOSTINFO");
}
public bool IsNull
{
get { return this._isNull; }
}
public static OdciCost Null
{
get { return new OdciCost { _isNull = true }; }
}
}
顺便说一句,我确定数据库连接的用户有权执行对象类型 –
查询如何查看?我测试了你的代码,它运行良好,但值没有映射。包含自定义类型的程序集是否可以加载? – Husqvik
嗨,实际上,查询sql是PL/SQL,但它确实很复杂。 我已经确认类成员和udt是相同的,为什么值不能被映射? 对不起,我不太了解你的第二个问题,所有这些代码都在一个dll中,而另一个项目引用了dll,并调用它的方法。 –