将Decimal数组传入Oracle存储过程时触发InvalidCastException异常

问题详情 #

存储过程定义 #

PROCEDURE SPName(
    ID IN Int,
    BID IN Varchar2,
    Secroleid IN Decimal,
    Site IN Varchar2
) AS

C#调用代码 #

public async Task<ProductUserRolesReplyItem> SaveProductUserRolesMultiplePcsNew(decimal pcid, List<ProductUserRoleSaveItem> productUserRoles)
{
    var query = string.Empty;
    ProductUserRolesReplyItem? productUserRolesReplyItem = null;
    var connectionstring = _appDBContext.GetConn();
    try
    {
        using (var conn = connectionstring)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            OracleCommand command = new OracleCommand
            {
                Connection = conn,
                CommandType = CommandType.StoredProcedure,
                CommandText = "SPName",
                BindByName = true
            };

            foreach (ProductUserRoleSaveItem pURole in productUserRoles)
            {
                var numRecs = pURole.ProductUserRoles.Count();
                var ids = new string[numRecs];
                var roleids = new Decimal[numRecs];
                var sites = new string[numRecs];
                var i = 0;

                foreach (var productUserRole in pURole.ProductUserRoles)
                {
                    idsids[i] = productUserRole.Idsid; // 笔误：应为ids[i]
                    ids[i] = productUserRole.Role.Security_Role_Id; // 笔误：应为roleids[i]
                    sites[i++] = productUserRole.Site;
                }
                
                command.Parameters.Add(new OracleParameter("ID", OracleDbType.Decimal, ParameterDirection.Input)).Value = pcid;
                command.Parameters.Add(new OracleParameter("BID", OracleDbType.Varchar2, ParameterDirection.Input)).Value = ids; // 传入数组，但存储过程接受单个Varchar2
                command.Parameters.Add(new OracleParameter("Secroleid", OracleDbType.Decimal, ParameterDirection.Input)).Value = roleids.ToArray(); // 传入数组，但存储过程接受单个Decimal
                command.Parameters.Add(new OracleParameter("Site", OracleDbType.Varchar2, ParameterDirection.Input)).Value = sites; // 传入数组，但存储过程接受单个Varchar2
                if (numRecs > 0)
                {
                    command.ExecuteNonQueryAsync().Wait(); // 异步方法用Wait易导致死锁
                    command.Transaction.Commit(); // 未开启事务就提交
                }
            }
        }
    }
    catch (Exception ex)
    {
        return new ProductUserRolesReplyItem
        {
            IsSuccess = false,
            Message = System.String.Format("Error encountered while saving product user roles: {0}", ex.Message)
        };
    }
    return new ProductUserRolesReplyItem { IsSuccess = true };
}

错误信息 #

InvalidCastException: Unable to cast object of type 'Oracle.ManagedDataAccess.Types.OracleDecimal[]' to type 'System.IConvertible'

错误原因 #

1. 参数类型不匹配：存储过程定义的BID、Secroleid、Site都是单个值类型，但C#代码中传入的是数组（ids、roleids、sites），Oracle驱动尝试将数组转换为单个OracleDecimal时失败，抛出类型转换异常。
2. 代码笔误：循环赋值时出现数组名称错误（idsids[i]应为ids[i]，ids[i]赋值给Security_Role_Id应为roleids[i]）。
3. 事务管理错误：未显式开启事务就调用Commit()，会引发异常。
4. 异步方法使用不当：调用ExecuteNonQueryAsync().Wait()会阻塞线程，存在死锁风险，不符合异步编程规范。

修正方案 #

方案1：循环调用存储过程处理单条数据 #

如果存储过程仅支持单条数据处理，修改代码循环遍历每条记录，逐个调用存储过程：

public async Task<ProductUserRolesReplyItem> SaveProductUserRolesMultiplePcsNew(decimal pcid, List<ProductUserRoleSaveItem> productUserRoles)
{
    var connection = _appDBContext.GetConn();
    try
    {
        using (connection)
        {
            if (connection.State == ConnectionState.Closed)
            {
                await connection.OpenAsync();
            }

            // 开启事务
            using (var transaction = connection.BeginTransaction())
            {
                foreach (var pURole in productUserRoles)
                {
                    foreach (var productUserRole in pURole.ProductUserRoles)
                    {
                        using (var command = new OracleCommand
                        {
                            Connection = connection,
                            CommandType = CommandType.StoredProcedure,
                            CommandText = "SPName",
                            BindByName = true,
                            Transaction = transaction
                        })
                        {
                            // 添加单个值参数
                            command.Parameters.Add(new OracleParameter("ID", OracleDbType.Decimal, ParameterDirection.Input)).Value = pcid;
                            command.Parameters.Add(new OracleParameter("BID", OracleDbType.Varchar2, ParameterDirection.Input)).Value = productUserRole.Idsid;
                            command.Parameters.Add(new OracleParameter("Secroleid", OracleDbType.Decimal, ParameterDirection.Input)).Value = productUserRole.Role.Security_Role_Id;
                            command.Parameters.Add(new OracleParameter("Site", OracleDbType.Varchar2, ParameterDirection.Input)).Value = productUserRole.Site;

                            await command.ExecuteNonQueryAsync();
                        }
                    }
                }

                // 提交事务
                transaction.Commit();
            }
        }

        return new ProductUserRolesReplyItem { IsSuccess = true };
    }
    catch (Exception ex)
    {
        return new ProductUserRolesReplyItem
        {
            IsSuccess = false,
            Message = $"Error encountered while saving product user roles: {ex.Message}"
        };
    }
}

方案2：修改存储过程接受集合参数（批量处理） #

如果需要批量处理数据，先在Oracle中定义集合类型，再修改存储过程接受集合参数：

1. 定义Oracle集合类型

CREATE OR REPLACE TYPE Varchar2Array AS TABLE OF VARCHAR2(200);
CREATE OR REPLACE TYPE DecimalArray AS TABLE OF NUMBER;

2. 修改存储过程

PROCEDURE SPName(
    ID IN Int,
    BID IN Varchar2Array,
    Secroleid IN DecimalArray,
    Site IN Varchar2Array
) AS
BEGIN
    -- 批量处理逻辑，例如循环遍历集合插入数据
    FOR i IN 1..BID.COUNT LOOP
        -- 执行你的业务逻辑，比如插入到表中
        -- INSERT INTO your_table (id, bid, secroleid, site) VALUES (ID, BID(i), Secroleid(i), Site(i));
    END LOOP;
END;

3. 修改C#代码传入集合参数

public async Task<ProductUserRolesReplyItem> SaveProductUserRolesMultiplePcsNew(decimal pcid, List<ProductUserRoleSaveItem> productUserRoles)
{
    var connection = _appDBContext.GetConn();
    try
    {
        using (connection)
        {
            if (connection.State == ConnectionState.Closed)
            {
                await connection.OpenAsync();
            }

            using (var transaction = connection.BeginTransaction())
            {
                foreach (var pURole in productUserRoles)
                {
                    var numRecs = pURole.ProductUserRoles.Count();
                    var ids = new string[numRecs];
                    var roleids = new decimal[numRecs];
                    var sites = new string[numRecs];
                    var i = 0;

                    foreach (var productUserRole in pURole.ProductUserRoles)
                    {
                        ids[i] = productUserRole.Idsid;
                        roleids[i] = productUserRole.Role.Security_Role_Id;
                        sites[i++] = productUserRole.Site;
                    }

                    using (var command = new OracleCommand
                    {
                        Connection = connection,
                        CommandType = CommandType.StoredProcedure,
                        CommandText = "SPName",
                        BindByName = true,
                        Transaction = transaction
                    })
                    {
                        // 传入集合参数，指定OracleDbType为对应的数组类型
                        command.Parameters.Add(new OracleParameter("ID", OracleDbType.Decimal, ParameterDirection.Input)).Value = pcid;
                        command.Parameters.Add(new OracleParameter("BID", OracleDbType.Array, ParameterDirection.Input))
                            .CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                        command.Parameters["BID"].Value = ids;
                        command.Parameters["BID"].Size = numRecs;

                        command.Parameters.Add(new OracleParameter("Secroleid", OracleDbType.Array, ParameterDirection.Input))
                            .CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                        command.Parameters["Secroleid"].Value = roleids;
                        command.Parameters["Secroleid"].Size = numRecs;

                        command.Parameters.Add(new OracleParameter("Site", OracleDbType.Array, ParameterDirection.Input))
                            .CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                        command.Parameters["Site"].Value = sites;
                        command.Parameters["Site"].Size = numRecs;

                        await command.ExecuteNonQueryAsync();
                    }
                }

                transaction.Commit();
            }
        }

        return new ProductUserRolesReplyItem { IsSuccess = true };
    }
    catch (Exception ex)
    {
        return new ProductUserRolesReplyItem
        {
            IsSuccess = false,
            Message = $"Error encountered while saving product user roles: {ex.Message}"
        };
    }
}

额外注意事项 #

• 确保Oracle.ManagedDataAccess NuGet包版本与.NET Core 8兼容。
• 异步方法中始终使用await而非Wait()，避免线程阻塞和死锁。
• 事务需显式开启并在所有操作完成后提交，异常时回滚（可在catch块中添加transaction.Rollback()）。

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