解决C# ADO.NET调用Oracle存储过程的类型转换错误

调用插入数组的接口时，遇到以下错误：
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Array'

已创建自定义VARRAY类型metric_array varray(10) varchar2(255)，并将其添加到Oracle PL/SQL存储过程与表字段中，以下是相关代码及报错信息：

C#代码片段 #

cmd.Parameters.Add("p_account_no", newConcession.AccountNumber);
cmd.Parameters.Add("p_customer_no", newConcession.CustomerNumber);
cmd.Parameters.Add("p_customer_name", newConcession.CustomerName);
cmd.Parameters.Add("p_old_rate", newConcession.OldRate);
cmd.Parameters.Add("p_proposed_rate", newConcession.ProposedRate);
cmd.Parameters.Add("p_product", newConcession.Product);
cmd.Parameters.Add("p_product_type", newConcession.ProductType);
cmd.Parameters.Add("p_concession_type", newConcession.ConcessionType);
cmd.Parameters.Add("p_old_fee", newConcession.OldFee);
cmd.Parameters.Add("p_proposed_fee", newConcession.ProposedFee);
cmd.Parameters.Add("p_justification", newConcession.Justification);
cmd.Parameters.Add("p_commitment", newConcession.Commitment);
cmd.Parameters.Add("p_liability_impact", newConcession.LiabilityImpact);
cmd.Parameters.Add("p_revenue_impact", newConcession.RevenueImpact);
cmd.Parameters.Add("p_concession_details", newConcession.ConcessionDetails);
cmd.Parameters.Add("p_approval_stage", newConcession.ApprovalStage);
cmd.Parameters.Add("p_line_manager", LineManagerName);
cmd.Parameters.Add("p_bulk_upload_id", newConcession.BulkUploadId);
cmd.Parameters.Add("p_maker_id", StaffId);
//cmd.Parameters.Add("p_maker_date_time", newConcession.MakerDateTime);
cmd.Parameters.Add("p_modifier_id", newConcession.ModifierId);
cmd.Parameters.Add("p_modified_date_time", newConcession.ModifiedDateTime);
//cmd.Parameters.Add("p_record_stat", newConcession.RecordStat);
cmd.Parameters.Add("p_concession_creation_type", newConcession.ConcessionCreationType);
cmd.Parameters.Add("p_covenant_start_date", newConcession.CovenantStartDate);
cmd.Parameters.Add("p_covenant_end_date", newConcession.CovenantEndDate);
cmd.Parameters.Add("p_covenant_amount", newConcession.CovenantAmount);
cmd.Parameters.Add("p_reference_number", newConcession.ReferenceNumber);
cmd.Parameters.Add("p_created_at", createdAt);
cmd.Parameters.Add("p_updated_at", createdAt);
cmd.Parameters.Add("p_document_path", "koshi danu");
cmd.Parameters.Add("p_line_manager_number", newConcession.LineManagerNumber);
cmd.Parameters.Add("p_branch_name", newConcession.BranchName);
cmd.Parameters.Add("p_region", newConcession.Region);
cmd.Parameters.Add("p_business_segment", newConcession.BusinessSegment);
cmd.Parameters.Add("p_operational_region", newConcession.OperationalRegion);
cmd.Parameters.Add("p_actual", newConcession.Actual);
cmd.Parameters.Add("p_area", newConcession.Area);
cmd.Parameters.Add("p_approval_date", newConcession.ApprovalDate);
cmd.Parameters.Add("p_review_date", newConcession.ReviewDate);
cmd.Parameters.Add("p_category", newConcession.Category);

Console.WriteLine("This is the valueeeeeee of concession metrics" + newConcession.ConcessionMetrics.ToString());
//var metricArray = newConcession.ConcessionMetrics.ToArray() ?? Array.Empty<string>();
//var metricArray = newConcession.ConcessionMetrics.ToArray();

//List<string> metricArray = newConcession.ConcessionMetrics;
if (newConcession != null && newConcession.ConcessionMetrics != null)
{
    Console.WriteLine("Concession Metrics:");
    foreach (var metric in newConcession.ConcessionMetrics)
    {
        Console.WriteLine(metric);
    }
}
else
{
    Console.WriteLine("ConcessionMetrics is null or empty");
}

List<string> metricList = newConcession.ConcessionMetrics ?? new List<string>();
string[] metricArray = metricList.ToArray();

//string metricString = string.Join(",", metricArray);
OracleParameter arrayParam = new OracleParameter
{
    ParameterName = "p_metric_array",
    OracleDbType = OracleDbType.Array,
    UdtTypeName = "METRIC_ARRAY_TYPE",
    CollectionType = OracleCollectionType.None,
    Direction = ParameterDirection.Input,
    Size = metricArray.Length,
    Value = metricArray.ToArray()
};

cmd.Parameters.Add(arrayParam);
cmd.ArrayBindCount = metricArray.Length;

cmd.BindByName = true;
await connection.OpenAsync();

OracleParameter concessionIdParam = new OracleParameter
{
    ParameterName = "p_concession_id",
    OracleDbType = OracleDbType.Decimal,
    Direction = ParameterDirection.Output
};

cmd.Parameters.Add(concessionIdParam);

await cmd.ExecuteNonQueryAsync();

OracleDecimal concessionIdDecimal = (OracleDecimal)cmd.Parameters["p_concession_id"].Value;
decimal decimalValue = concessionIdDecimal.Value;
int concessionId = Convert.ToInt32(decimalValue);
Console.WriteLine("this is concessionid type " + concessionId.GetType().ToString());

ConcessionMetrics定义 #

public List<string> ConcessionMetrics { get; set; }

Oracle存储过程部分代码 #

p_metric_array metric_array_type
returning concession_id into p_concession_id;

update new_concession_tbl
set metric_array = p_metric_array
where concession_id = p_concession_id;
commit;

报错堆栈 #

System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Array'.
   at Oracle.ManagedDataAccess.Client.OracleParameter.SetNullIndicators()
   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)
   --- End of stack trace from previous location ---
   at Alero.DataSource.ConcessionRepoImpl.AddNewConcession(NewConcession newConcession, List`1 documentTitles, List`1 documentDescriptions, List`1 documents, String userId) in C:\Users\oilesanmi\Documents\Repositories\Alero-Api\Alero.DataSource\ConcessionRepoImpl.cs:line 168

解决方案 #

1. 修正OracleParameter配置
   当前代码中OracleDbType设置为OracleDbType.Array、CollectionType设置为OracleCollectionType.None是错误的。正确配置需匹配VARRAY的元素类型，并指定正确的集合类型：

   OracleParameter arrayParam = new OracleParameter
   {
       ParameterName = "p_metric_array",
       OracleDbType = OracleDbType.Varchar2, // 对应VARRAY的元素类型VARCHAR2(255)
       UdtTypeName = "METRIC_ARRAY_TYPE", // 确保与Oracle中自定义类型名完全一致
       CollectionType = OracleCollectionType.Array,
       Direction = ParameterDirection.Input,
       Value = metricArray // 直接传入string[]，无需重复ToArray()
   };
   

2. 移除ArrayBindCount设置
   cmd.ArrayBindCount用于批量SQL操作，传递单个数组参数时不需要此设置，删除cmd.ArrayBindCount = metricArray.Length;这一行。

3. 确保参数值不为null
   当ConcessionMetrics为空时，传递Array.Empty<string>()而非null，避免类型转换异常：

   string[] metricArray = (newConcession.ConcessionMetrics ?? new List<string>()).ToArray() ?? Array.Empty<string>();
   

4. 验证自定义类型名称
   确认Oracle中创建的VARRAY类型名称与UdtTypeName完全一致，比如如果创建语句是CREATE OR REPLACE TYPE metric_array_type AS VARRAY(10) OF VARCHAR2(255);，则当前配置正确；如果类型名是metric_array，则需要修改UdtTypeName为"METRIC_ARRAY"（注意Oracle大小写规则）。

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