Copy data to Oracle destination

We are trying to copy data to an Oracle DWH, and we are facing issue when trying with different setups on the “Write Batch Size” parameter.

The copy activity works when we set the “Write Batch Size” to 1, but of course performances are bad, it writes about 10.000 rows in 5 minutes.

To speed up copy we are trying to set the parameter to the default value of 10.000

But in this case, copy data fails with the following error:

Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
Error in parameter 1.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
Error in parameter 1.,Source=msora28.dll,'

So far we have INSERT privileges on Oracle Schema (in fact writing works with parameter = 1 and using direct SQL), but it looks like something different is used with the default value on Write Batch Size

We don’t want to focus on the error message, it is obvious that it has been raised on the Oracle Side.

But we need more information in order to understand what’s causing the issue. It looks like ADF is using two different ways to copy data depending on the value of the parameter.

Any help would be greatly appreciated.

Thanks in advance

Alessandro