ODP.NET

Hi All -

I am running into an issue where I keep receiving the following error when trying to use Array Binding. I am dynamically enumerating through a DataSet and populating tables in Oracle of the same name with all of the DataTable data. I've tried debugging just with one single datatable of 10 records with datatype double, however still receive the exception. For reference, the connection is already opened before calling this method.

public void BulkLoadWithArrayBinding(System.Data.DataTable dt)
{
StringBuilder sb = new StringBuilder();
List<OracleParameter> parameters = new List<OracleParameter>(dt.Columns.Count);

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;

sb.Append("INSERT INTO " + dt.TableName + " (");
foreach (DataColumn dc in dt.Columns)
{
sb.Append(dc.ColumnName.ToUpper());
if (dc.Ordinal < dt.Columns.Count - 1)
sb.AppendLine(",");
}
sb.Append(") VALUES(");

foreach (DataColumn dc in dt.Columns)
{
string parameterName = dc.ColumnName.ToUpper();

sb.Append(":" + parameterName);
if(dc.Ordinal < dt.Columns.Count - 1)
sb.AppendLine(",");

String[] sArray = new String[dt.Rows.Count];
DateTime[] dArray = new DateTime[dt.Rows.Count];
Decimal[] dbArray = new Decimal[dt.Rows.Count];

OracleParameter p = null;
if (dc.DataType.Name == "String")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
sArray[i] = dt.Rows[i][dc.Ordinal].ToString();
}

p = new OracleParameter(parameterName,
OracleDbType.Varchar2, dt.Rows.Count, ParameterDirection.Input);
p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p.Size = sArray.Length;
p.Value = sArray;
}
else if (dc.DataType.Name == "DateTime")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dArray[i] = Convert.ToDateTime(dt.Rows[i][dc.Ordinal]);
}

p = new OracleParameter(parameterName,
OracleDbType.Date, dt.Rows.Count, ParameterDirection.Input);
p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p.Size = dArray.Length;
p.Value = dArray;
}
else
if (dc.DataType.Name == "Double")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dbArray[i] = Convert.ToDecimal(dt.Rows[i][dc.Ordinal]);
}

/*p = new OracleParameter(parameterName,OracleDbType.Decimal, dbArray.Length, ParameterDirection.Input);*/
p = new OracleParameter();
p.ParameterName = parameterName;
p.OracleDbType = OracleDbType.Decimal;
p.Direction = ParameterDirection.Input;
p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p.Size = dbArray.Length;
p.Value = dbArray;
}

cmd.Parameters.Add(p);
//parameters.Add(p);
}

sb.AppendLine(")");

cmd.CommandText = sb.ToString();
cmd.CommandType = CommandType.Text;
cmd.ArrayBindCount = dt.Rows.Count;
cmd.BindByName = true;

cmd.ExecuteNonQuery();
}