.NET如何调用带复杂参数的Oracle存储过程_自定义类型

Oracle UDT 与 Table Type 使用指南：数据库预创建与 ODP.NET 参数配置详解

Oracle UDT 和 Table Type 必须在数据库中预先创建

在 C# 应用程序中调用 Oracle 存储过程时，如果涉及自定义对象类型（UDT）或表类型（Table Type），必须确保这些类型已在 Oracle 数据库中成功创建并处于有效状态。若数据库端类型定义缺失或无效，即使 C# 代码参数配置正确，系统仍会抛出 ORA-04043: object ... does not exist 或 ORA-00902: invalid datatype 等错误。问题的核心在于数据库对象不存在，而非代码逻辑错误。

因此，在编写 C# 调用代码之前，务必在 Oracle 数据库中完成以下验证：

• 确认对象类型（OBJECT）已通过 CREATE OR REPLACE TYPE ... AS OBJECT 语句成功创建，且状态为 VALID。
• 确认表类型（TABLE OF ...）已通过 CREATE OR REPLACE TYPE ... AS TABLE OF ... 语句创建，并且其依赖的基础对象类型已存在。

常见错误场景是：先创建了 student_table_type，却未创建其依赖的 student_obj_type。这种情况下，程序可能在连接阶段不报错，但在执行存储过程时，会因类型解析失败而提示“类型不存在”。

ODP.NET 中传递 Table Type 需使用 OracleCollectionType.PLSQLAssociativeArray

在 .NET 中调用 Oracle 存储过程并传递表类型参数时，必须使用 Oracle 官方的 ODP.NET 数据提供程序（即 Oracle.ManagedDataAccess 库）。已过时的 System.Data.OracleClient 无法识别 PL/SQL 关联数组，不应继续使用。

配置 ODP.NET 参数时，需重点关注以下三个要点：

• 必须将 OracleParameter.CollectionType 属性设置为 OracleCollectionType.PLSQLAssociativeArray，而非 PLSQLNestedTable 或其他选项。
• OracleParameter.Value 必须赋值为 .NET 数组（如 object[] 或具体类型的数组），直接传入 List 或 IEnumerable 集合将导致错误。
• 存储过程中对应的输入参数必须声明为 IN my_table_type 形式，且 my_table_type 必须是数据库中已定义的 TABLE OF ... 类型。

以下是一个配置示例：

var students = new object[] {
    new object[] { 1, "Alice", DateTime.Now },
    new object[] { 2, "Bob", DateTime.Now }
};
var param = new OracleParameter("p_students", OracleDbType.Object);
param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param.UdtTypeName = "STUDENT_TABLE_TYPE"; // 注意：类型名需全大写，严格匹配数据库定义
param.Value = students;

UDT 对象数组需映射至 .NET 类并注册 OracleUdtMapping

若要传递自定义对象类型（如 TestObjType），而非简单字段组合，则必须为 ODP.NET 建立 .NET 类与 Oracle 对象类型之间的映射关系。仅在 C# 中定义类是不够的，必须进行显式的映射注册，否则无法正确序列化与反序列化数据。

映射注册主要有两种方式：

• 全局注册（推荐）：在应用程序启动时（如 Main 方法或 Global.asax 中）调用 OracleUdtMapping.Add 方法，一次性完成所有自定义类型的映射注册。
• 局部注册：在每次创建数据库连接前手动注册映射。此方式易遗漏，增加维护复杂度，一般不推荐。

注册代码示例如下（需确保命名空间、属性顺序及大小写与数据库定义完全一致）：

[OracleCustomTypeMapping("SCHEMA.TESTOBJTYPE")]
public class TestObjType : IOracleCustomType
{
    public decimal? ID { get; set; }
    public string Name { get; set; }
    public DateTime? CreateTime { get; set; }
    public string Status { get; set; }
    public string Description { get; set; }

    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        // 实现字段写入逻辑
    }
    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        // 实现字段读取逻辑
    }
}

// 在应用启动时注册映射：
OracleUdtMapping.Add(new OracleUdtMapping{
    UdtTypeName = "SCHEMA.TESTOBJTYPE",
    CSharpTypeName = typeof(TestObjType).AssemblyQualifiedName
});

若遗漏 [OracleCustomTypeMapping] 特性，或注册时类型名大小写不匹配（如数据库定义为 TESTOBJTYPE，代码中误写为 TestObjType），可能导致 ORA-03113: end-of-file on communication channel 错误或静默失败。

输出参数为 UDT 时，OracleParameter.Direction 须设为 Output 且不预设 Value

当存储过程返回 TABLE OF ... 类型的结果集时，输出参数的配置是常见错误点。若为输出参数预先赋值（如 param.Value = new object[0]），ODP.NET 会尝试向 Oracle 写入该空数组，从而引发类型不匹配异常。

正确的配置步骤如下：

• 将 Direction 属性设置为 ParameterDirection.Output
• 保持 Value 属性为空（不进行赋值）
• 命令执行完毕后，从 param.Value 属性中读取返回的 OracleUdt 或 object[] 数组

假设存储过程定义如下：

PROCEDURE get_students(p_out OUT student_table_type)

则 C# 端应如下配置：

var outParam = new OracleParameter("p_out", OracleDbType.Object);
outParam.Direction = ParameterDirection.Output;
outParam.UdtTypeName = "STUDENT_TABLE_TYPE";
outParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
cmd.Parameters.Add(outParam);

cmd.ExecuteNonQuery();

// 执行完成后读取结果：
var resultArray = (object[])outParam.Value;

需注意一个细节：UDT 输出结果中的每个元素默认是 OracleUdt 实例，不会自动转换为已定义的 .NET 类——除非已实现并注册完整的序列化逻辑。若直接进行强制类型转换，将抛出 InvalidCastException 异常。