Why does this oracle bulk insert not work?

问题:

I am trying to bulk insert some data into an oracle db. I followed the example in the documentation.

this.DataBaseAccess = new OracleConnection(connString);
var dataAdapter = new OracleDataAdapter();
var insertCmd = DataBaseAccess.CreateCommand();
insertCmd.CommandType = CommandType.Text;
insertCmd.BindByName = true;

var names = new List<string>();

foreach (DataTable table in product.Contracts.Tables)
{
    foreach (DataRow row in table.Rows)
    {
        names.Add(row["Contract"].ToString());
    }

    const string InsertContracts = "merge into CONTRACT t " +
                                   "using " +
                                   "(select :name NAME from dual) s " +
                                   "on (t.NAME = s.NAME) " +
                                   "when not matched then " +
                                   "insert (t.NAME) " +
                                   "values (s.NAME)";

    insertCmd.CommandText = InsertContracts;
    insertCmd.ArrayBindCount = table.Rows.Count;
    insertCmd.Parameters.Add(":name", OracleDbType.Varchar2, names, ParameterDirection.Input);

    dataAdapter.InsertCommand = insertCmd;

    this.DataBaseAccess.Open();
    insertCmd.ExecuteNonQuery();
    this.DataBaseAccess.Close();
}

Well, it does not work. Nothing is inserted into the database, I don't get any error messages.

Everything works fine when i do not use bulk insert (instead i foreach-loop through each row from my DataTables and insert the DataRow into the Database on each iteration).

UPDATE: I have followed the suggestions and made the following changes to my parameter.

var nameParam = new OracleParameter
    {
        ParameterName = ":name",
        OracleDbType = OracleDbType.Varchar2,
        Value = names,
        Size = table.Rows.Count,
        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
        Direction = ParameterDirection.Input
    }; 

I get this error:

System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Array'.
at Oracle.DataAccess.Client.OracleParameter.SetStatus(Int32 arraySize)
at Oracle.DataAccess.Client.OracleParameter.ResetCtx(Int32 arraySize)
at Oracle.DataAccess.Client.OracleParameter.PreBind(OracleConnection conn, IntPtr errCtx, Int32 arraySize)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at Gateway.DataGateway.Import(String connString, Product product) in \path\share$\Visual Studio 2010\Projects\ImportData-trunk\Gateway\DataGateway.Sql.cs:line 196

UPDATE2: The ODP.NET driver is stupid (just does not work as i exprected ;)

this does not work

var names = new List<string>();

it has to be this

var names = new string[table.Rows.Count];

回答1:

You need to set the parameter CollectionType property to OracleCollectionType.PLSQLAssociativeArray to get bulk operations to work.

Since there's no Add() method that allows you to specificy this, you would have to add the following line after the call to Parameters.Add():

insertCmd.Parameters[0].CollectionType = OracleCollectionType.PLSQLAssociativeArray

回答2:

private void BulkCopy(List<test_bulk> lsttest_bulk)
    {       
        try
        {
            //ConnectionString = String.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4};", "ServerAddress", "PortAddress", "DatabaseName", "Username", "Password");

            ConnectionString = String.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4};", "ServerAddress", "PortAddress", "DatabaseName", "Username", "Password");


            OracleConnection oraConn = new OracleConnection(ConnectionString);

            oraConn.Open();
            OracleCommand oraCMD = new OracleCommand();
            oraCMD.Connection = oraConn;



            var oracleBulkCopy = new OracleBulkCopy(oraConn)
            {
                DestinationTableName = "test_bulk",
                BulkCopyOptions = OracleBulkCopyOptions.UseInternalTransaction
            };

                DataTable oDataTable = GetDataTableFromObjects<test_bulk>(lsttest_bulk);

                oracleBulkCopy.WriteToServer(oDataTable);
                oracleBulkCopy.Dispose();
        }
        catch(Exception ex)
        {
            Console.WriteLine("failed to write:\t{0}", ex.Message);
        }
    }


    public static DataTable GetDataTableFromObjects<TDataClass>(List<TDataClass> dataList)
    where TDataClass : class
    {
        Type t = typeof(TDataClass);
        DataTable dt = new DataTable(t.Name);
        foreach (PropertyInfo pi in t.GetProperties())
        {
            dt.Columns.Add(new DataColumn(pi.Name));
        }
        if (dataList != null)
        {
            foreach (TDataClass item in dataList)
            {
                DataRow dr = dt.NewRow();
                foreach (DataColumn dc in dt.Columns)
                {
                    dr[dc.ColumnName] =
                      item.GetType().GetProperty(dc.ColumnName).GetValue(item, null);
                }
                dt.Rows.Add(dr);
            }
        }
        return dt;
    }

回答3:

I had to us the ToArray() method on the List.

insertCmd.Parameters.Add(":name", OracleDbType.Varchar2, names.ToArray(), ParameterDirection.Input);