ODP.NET 18.3 Bulkinsert with Nullable Types

user10709180

I'm trying to get bulkinsert working in a .NET application.

I have a lot of string columns, a few int columns that al seem to work when my data POCO properties are of string or int.

The bulk insert fails to work when I try to insert a Nullable int (int?) as source! Which I didn't expect as the column in the table is nullable, and should be able to handle null.

Mind you, although the POCO property was int?, it had values for all rows the array I extracted from it. So it couldn't be a <null> problem.

The exception 'System.ArgumentException' (Value does not fall within the expected range) is thrown when I try to add the array to the Parameter property of the command. (So we don't even get to the database).

I'm using ODP.NET 18.3 as dataprovider. The application is written in C# 3.5.

So I have the following questions:

1) Can I use a Nullable type (int?, datetime?) in ODP.NET BulkInsert?

2) If the answer to the previous anwer is a yes, what should I take in consideration? What am I missing?

POCO

public class MyPoco

{

        public int? MyInt { get; set; }

}

The code that extracts the array of values.

                // Get the list of values (as object)

                var valueObjectList = dataEntityList

                    .Select(item => propInfo.GetValue(item))

                    .ToList();

                var parameter = command.CreateParameter();

                parameter.Direction = ParameterDirection.Input;

                parameter.IsNullable = true;

                parameter.ParameterName = ":" + propInfo.Name;

                dynamic result = null;

                if (propertyType == typeof(int?))

                {

                    result = ConvertToTypedList<int?>(valueObjectList);

                    parameter.Value = result; <----- this gives an ArgumentException

               // More code that is unimportant.

'result' had values in it!

Regards

Marc

Alex Keh-Oracle

When inserting with Bulk Copy, NOT NULL is automatically enabled by default. You will find the full list of restrictions to use ODP.NET Bulk Copy here: https://docs.oracle.com/en/database/oracle/oracle-data-access-components/18.3/odpnt/featBulkCopy.html#GUID-91569C5F-52C3-4F95-8040-432A8AA8D0F8

user10709180

I'm not talking about a bulkcopy from one DB to another DB, i'm talking about a bulkinsert, which is implemented bij the ODP.NET managed driver. I can bulkinsert with .NET types as strings and int, but not with an nullable int.

I don't see how this answer has anything to do with the question i asked.

Alex Keh-Oracle

It's not clear what feature you mean by "BulkInsert". There's a number of ways to use ODP.NET to insert bulk data into the DB.

3909474

If we are using ArrayBinding to bulkinsert data into Oracle tables, how do we insert null values for decimal columns?