从Oracle包向C#返回复杂对象遇MYPKG.PARCEL_TABLE无效错误

云原生阿岚

2026-6-29

问题：C#调用Oracle嵌套UDT存储过程报错"MYPKG.PARCEL_TABLE is invalid"

问题背景

尝试从C#调用Oracle包内的存储过程，获取包含父记录（devsite）和多个子记录（parcels）的复杂返回对象。Oracle端存储过程可正常执行，但C#配置UDT映射后调用时抛出类型无效错误。

Oracle包定义

TYPE PARCEL_Record IS RECORD (
    PIN varchar2(100),
    ADDRESS varchar2(100)
);

TYPE PARCEL_Table IS TABLE OF PARCEL_Record;

TYPE DEVSITE_Record IS RECORD (
    PRCLID varchar2(100),
    PARCELS PARCEL_Table
);

PROCEDURE Test(DevsiteId IN varchar2, Output OUT DEVSITE_Record);

Oracle包体实现

PROCEDURE Test(DevsiteId IN varchar2, Output OUT DEVSITE_Record) IS
BEGIN
    Output.PRCLID := DevsiteId;
    
    Output.PARCELS := PARCEL_Table();
    
    Output.PARCELS.extend;
    Output.PARCELS(1).PIN := 'PIN1';
    Output.PARCELS(1).ADDRESS := 'Address 1';

    Output.PARCELS.extend;
    Output.PARCELS(2).PIN := 'PIN2';
    Output.PARCELS(2).ADDRESS := 'Address 2';

    Output.PARCELS.extend;
    Output.PARCELS(3).PIN := 'PIN3';
    Output.PARCELS(3).ADDRESS := 'Address 3';
    
END;

C# UDT映射代码

[OracleCustomTypeMapping("MYPKG.PARCEL_RECORD")]
public class ParcelDataFactory : IOracleCustomTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new ParcelData();
    }
}

public class ParcelData : IOracleCustomType, INullable
{
    [OracleObjectMapping("PIN")]
    public string PIN { get; set; }

    [OracleObjectMapping("ADDRESS")]
    public string ADDRESS { get; set; }

    public bool IsNull { get; private set; }

    public static ParcelData Null { }

    public void FromCustomObject(OracleConnection con, object udt) { }
    public void ToCustomObject(OracleConnection con, object udt) { }
}

[OracleCustomTypeMapping("MYPKG.PARCEL_TABLE")]
public class ParcelTableFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{ 
    public IOracleCustomType CreateObject()
    {
        return new ParcelTable();
    }

    public Array CreateArray(int numElems)
    {
        return new ParcelData[numElems];
    }

    public Array CreateStatusArray(int numElems)
    {
        return new OracleUdtStatus[numElems];
    }
}

public class ParcelTable : IOracleCustomType, INullable
{
    [OracleArrayMapping()]
    public ParcelData[] Parcels { get; set; }

    public bool IsNull { get; private set; }

    public static ParcelTable Null{ }

    public void FromCustomObject(OracleConnection con, object udt) { }
    public void ToCustomObject(OracleConnection con, object udt) { }
}

[OracleCustomTypeMapping("MYPKG.DEVSITE_RECORD")]
public class DevsiteDataFactory : IOracleCustomTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new DevsiteData();
    }
}

public class DevsiteData : IOracleCustomType, INullable
{
    [OracleObjectMapping("PRCLID")]
    public string PRCLID { get; set; }

    [OracleObjectMapping("PARCELS")]
    public ParcelTable Parcels { get; set; }

    public bool IsNull { get; private set; }

    public static DevsiteData Null { }

    public void FromCustomObject(OracleConnection con, object udt) { }
    public void ToCustomObject(OracleConnection con, object udt) { }
}

C#调用代码

using (var con = new OracleConnection(ConnectionString))
{
    using (var cmd = con.CreateCommand())
    {
        await con.OpenAsync();

        cmd.CommandText = "MYPKG.Test";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("DevsiteId", devsiteId);

        var outParameter = cmd.Parameters.Add(
            new OracleParameter()
            {
                Direction = ParameterDirection.Output,
                OracleDbType = OracleDbType.Object,
                ParameterName = "Output",
                UdtTypeName = "MYPKG.DEVSITE_Record"
            });

        await cmd.ExecuteNonQueryAsync();

        return (DevsiteData)outParameter.Value;
    }
}

错误信息

System.InvalidOperationException: MYPKG.PARCEL_TABLE is invalid
   at 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.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
   at System.Data.Common.DbCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken)

错误原因

Oracle Managed Data Access（OMDA）不支持包内定义的集合/记录类型作为UDT使用。包内类型属于包的私有成员，OMDA无法读取其元数据，因此报错"MYPKG.PARCEL_TABLE is invalid"。必须使用数据库级别的自定义类型（通过CREATE TYPE独立创建，而非包内定义）。

解决方案

1. 创建数据库级别自定义类型

-- 创建Parcel记录类型（数据库级别）
CREATE OR REPLACE TYPE PARCEL_RECORD AS OBJECT (
    PIN varchar2(100),
    ADDRESS varchar2(100)
);
/

-- 创建Parcel表类型（数据库级别）
CREATE OR REPLACE TYPE PARCEL_TABLE AS TABLE OF PARCEL_RECORD;
/

-- 创建Devsite记录类型（数据库级别）
CREATE OR REPLACE TYPE DEVSITE_RECORD AS OBJECT (
    PRCLID varchar2(100),
    PARCELS PARCEL_TABLE
);
/

2. 修改Oracle包，引用数据库级别类型

CREATE OR REPLACE PACKAGE MYPKG AS
    PROCEDURE Test(DevsiteId IN varchar2, Output OUT DEVSITE_RECORD);
END MYPKG;
/

CREATE OR REPLACE PACKAGE BODY MYPKG AS
    PROCEDURE Test(DevsiteId IN varchar2, Output OUT DEVSITE_RECORD) IS
    BEGIN
        Output := DEVSITE_RECORD(DevsiteId, PARCEL_TABLE());
        
        Output.PARCELS.extend;
        Output.PARCELS(1) := PARCEL_RECORD('PIN1', 'Address 1');

        Output.PARCELS.extend;
        Output.PARCELS(2) := PARCEL_RECORD('PIN2', 'Address 2');

        Output.PARCELS.extend;
        Output.PARCELS(3) := PARCEL_RECORD('PIN3', 'Address 3');
        
    END;
END MYPKG;
/

3. 调整C# UDT映射代码

修正类型映射名称（去掉包名，使用数据库级别类型名），并实现完整的序列化/反序列化逻辑：

[OracleCustomTypeMapping("PARCEL_RECORD")]
public class ParcelDataFactory : IOracleCustomTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new ParcelData();
    }
}

public class ParcelData : IOracleCustomType, INullable
{
    [OracleObjectMapping("PIN")]
    public string PIN { get; set; }

    [OracleObjectMapping("ADDRESS")]
    public string ADDRESS { get; set; }

    public bool IsNull { get; private set; }

    public static ParcelData Null { get; } = new ParcelData { IsNull = true };

    public void FromCustomObject(OracleConnection con, object udt)
    {
        OracleUdt.SetValue(con, udt, "PIN", PIN);
        OracleUdt.SetValue(con, udt, "ADDRESS", ADDRESS);
    }

    public void ToCustomObject(OracleConnection con, object udt)
    {
        PIN = OracleUdt.GetValue<string>(con, udt, "PIN");
        ADDRESS = OracleUdt.GetValue<string>(con, udt, "ADDRESS");
    }
}

[OracleCustomTypeMapping("PARCEL_TABLE")]
public class ParcelTableFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new ParcelTable();
    }

    public Array CreateArray(int numElems)
    {
        return new ParcelData[numElems];
    }

    public Array CreateStatusArray(int numElems)
    {
        return new OracleUdtStatus[numElems];
    }
}

public class ParcelTable : IOracleCustomType, INullable
{
    [OracleArrayMapping()]
    public ParcelData[] Parcels { get; set; }

    public bool IsNull { get; private set; }

    public static ParcelTable Null { get; } = new ParcelTable { IsNull = true };

    public void FromCustomObject(OracleConnection con, object udt)
    {
        OracleUdt.SetValue(con, udt, Parcels);
    }

    public void ToCustomObject(OracleConnection con, object udt)
    {
        Parcels = (ParcelData[])OracleUdt.GetValue(con, udt, 0);
    }
}

[OracleCustomTypeMapping("DEVSITE_RECORD")]
public class DevsiteDataFactory : IOracleCustomTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new DevsiteData();
    }
}

public class DevsiteData : IOracleCustomType, INullable
{
    [OracleObjectMapping("PRCLID")]
    public string PRCLID { get; set; }

    [OracleObjectMapping("PARCELS")]
    public ParcelTable Parcels { get; set; }

    public bool IsNull { get; private set; }

    public static DevsiteData Null { get; } = new DevsiteData { IsNull = true };

    public void FromCustomObject(OracleConnection con, object udt)
    {
        OracleUdt.SetValue(con, udt, "PRCLID", PRCLID);
        OracleUdt.SetValue(con, udt, "PARCELS", Parcels);
    }

    public void ToCustomObject(OracleConnection con, object udt)
    {
        PRCLID = OracleUdt.GetValue<string>(con, udt, "PRCLID");
        Parcels = (ParcelTable)OracleUdt.GetValue(con, udt, "PARCELS");
    }
}

4. 调整C#调用代码

修改UdtTypeName为数据库级别类型名：

using (var con = new OracleConnection(ConnectionString))
{
    using (var cmd = con.CreateCommand())
    {
        await con.OpenAsync();

        cmd.CommandText = "MYPKG.Test";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("DevsiteId", devsiteId);

        var outParameter = cmd.Parameters.Add(
            new OracleParameter()
            {
                Direction = ParameterDirection.Output,
                OracleDbType = OracleDbType.Object,
                ParameterName = "Output",
                UdtTypeName = "DEVSITE_RECORD" // 去掉包名，使用数据库级别类型
            });

        await cmd.ExecuteNonQueryAsync();

        return (DevsiteData)outParameter.Value;
    }
}

关键注意事项

OMDA仅支持数据库级别的CREATE TYPE定义的UDT，包内类型无法被识别
必须实现IOracleCustomType的FromCustomObject和ToCustomObject方法，否则无法完成数据的序列化/反序列化
类型名称大小写需与数据库一致（Oracle默认大写，除非创建时用双引号指定小写）

内容的提问来源于stack exchange，提问作者Paul Abbott

从Oracle包向C#返回复杂对象遇MYPKG.PARCEL_TABLE无效错误

问题背景 #

Oracle包定义 #

Oracle包体实现 #

C# UDT映射代码 #

C#调用代码 #

错误信息 #

错误原因 #

解决方案 #

1. 创建数据库级别自定义类型 #

2. 修改Oracle包，引用数据库级别类型 #

3. 调整C# UDT映射代码 #

4. 调整C#调用代码 #

关键注意事项 #