I am experiencing the error above whenever I hit an endpoint that tries to insert an array. I created a custom type and added it to my procedure. I added a column to my table and made it type the custom type that I created. The custom type is metric_array `` vararay(10) varchar2(255). I have battled this for a whole day.
This is what I have done so far. This is a snippet of my C# code
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());
This is what concessionMetrics look like
public List<string> ConcessionMetrics { get; set; }
here is my procedure too. Some parts
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;
This is the full-stack trace of the error I have
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
First of all I want to say that you are using ArrayBindCount incorrectly. This options for command means that all parameters are an array of a certain length. For example, this is very convenient for batch inserts.
Secondly, using UDT is not easiest way to pass collection to the database. If you want to pass UDT to the oracle from .net, you must comply with all requirements that are imposed. It can't be a simple array, .net type should to implement IOracleCustomType and INullable interfaces. You can check full sample at this link.
Easiest way to pass array to the pl/sql is using pl/sql associative array (table of ... index by ..., which you can define in the package header with your function/procedure):
const string sql =
@"declare
type t_arr is table of varchar2(255) index by pls_integer;
l_val t_arr;
l_concat varchar2(4000);
begin
l_val := :p_metric_array;
if l_val.count = 0 then
RAISE_APPLICATION_ERROR(-20000, 'Array is empty!');
end if;
l_concat := null;
FOR i IN l_val.FIRST .. l_val.LAST
LOOP
l_concat := l_concat || l_val(i);
END LOOP;
:p_result := l_concat;
end;";
string[] metricArray = { "asd", "qwe", "1", "2" };
using (OracleConnection con = new OracleConnection(conString))
{
con.Open();
using (OracleCommand cmd = con.CreateCommand())
{
OracleParameter arrayParam = new OracleParameter
{
ParameterName = "p_metric_array",
OracleDbType = OracleDbType.Varchar2, // <<<
CollectionType = OracleCollectionType.PLSQLAssociativeArray, // <<<
Direction = ParameterDirection.Input,
Size = metricArray.Length,
Value = metricArray
};
cmd.Parameters.Add(arrayParam);
OracleParameter outParameter = new OracleParameter
{
ParameterName = "p_result",
OracleDbType = OracleDbType.Varchar2,
Direction = ParameterDirection.Output,
Size = 4000
};
cmd.Parameters.Add(outParameter);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
Debug.WriteLine(outParameter.Value);
}
}
And you can check external sample.