Dapper OracleMappingType.RefCursor direction: ParameterDirection.Input not working

[GuibsonKrause]

In my C# I have a list of objects with values to be inserted into a table in my Oracle db, I'm using a foreach to scroll through the list and send record by record to a function in a pck and there I make an insert. I'm using dapper to do this. But the problem is that it takes too long when there are too many records. Ex 35000 inserts. I wonder if I can send the entire list to the bank and there I can do all the inserts at once. I thought about the possibility of using an OracleMappingType.RefCursor direction: ParameterDirection.Input but the RefCursor doesn't work for the input type only for output. Would anyone know how to tell me a way to pass the entire list to the db and there I can make the inserts?

The method below is the form I am using today but it takes a long time when I have a very large list of data.

string query = "PCK_TEST.FC_IMPORT_VALUES";

            foreach (var item in ListOfValues)
            {
                var param = new OracleDynamicParameters();

                param.Add("P_VALUE_A", dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input, value: item.ValueA);
                param.Add("P_VALUE_B", dbType: OracleMappingType.Date, direction: ParameterDirection.Input, value: item.ValueB);
                param.Add("P_VALUE_C", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueC);
                param.Add("P_VALUE_D", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueD);
                param.Add("P_VALUE_E", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueE);
                param.Add("P_VALUE_F", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueF);
                param.Add("P_VALUE_G", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueG);
                param.Add("P_VALUE_H", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueH);
                param.Add("P_VALUE_I", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueI);
                param.Add("P_VALUE_J", dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input, value: item.ValueJ);
                param.Add("P_VALUE_K", dbType: OracleMappingType.Decimal, direction: ParameterDirection.Input, value: item.ValueK);
                param.Add("P_VALUE_L", dbType: OracleMappingType.Varchar2, direction: ParameterDirection.Input, value: item.ValueL);
                param.Add("P_RETORN", dbType: OracleMappingType.Varchar2, size: 4000, direction: ParameterDirection.ReturnValue);

                await SqlMapper.ExecuteAsync(_con, query, param: param, commandType: CommandType.StoredProcedure);
                var erro = param.Get<string>("P_RETORN");
            }

[epaulsen]

Hi, you can use BulkSql, a built-in feature of Oracle .net driver and implemented in Dapper.Oracle.
Please see https://github.com/DIPSAS/Dapper.Oracle/blob/master/doc/BulkSql.md for details.

I've tested this approach with 2000+ records at once, but for 35.000 you might want do do it in separate operations.

Let me know if you have any further questions!

[GuibsonKrause]

Hi, you can use BulkSql, a built-in feature of Oracle .net driver and implemented in Dapper.Oracle.
Please see https://github.com/DIPSAS/Dapper.Oracle/blob/master/doc/BulkSql.md for details.

I've tested this approach with 2000+ records at once, but for 35.000 you might want do do it in separate operations.

Let me know if you have any further questions!

@epaulsen Thank you for your answer .
I traid this solution but when running _con.SqlBulk(query, listOfValues, mapping); row returned the Following error message:

System.ArgumentException: Value does not fall within the expected range.
   at Oracle.ManagedDataAccess.Client.OracleParameter.set_Value(Object value)
   at Dapper.Oracle.OracleDynamicParameters.AddParameters(IDbCommand command, Identity identity)
   at Dapper.Oracle.OracleDynamicParameters.Dapper.SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, Identity identity)
   at Dapper.SqlMapper.<>c__DisplayClass150_0.<GetCacheInfo>b__0(IDbCommand cmd, Object obj)
   at Dapper.CommandDefinition.SetupCommand(IDbConnection cnn, Action`2 paramReader)
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader)
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command)
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType)
   at Dapper.Oracle.BulkSql.BulkOperation.SqlBulk[T](IDbConnection connection, String sql, IEnumerable`1 objects, IEnumerable`1 mapping, OracleDynamicParameters& parameters, Nullable`1 cmdType, IDbTransaction transaction)
   at Dapper.Oracle.BulkSql.BulkOperation.SqlBulk[T](IDbConnection connection, String sql, IEnumerable`1 objects, IEnumerable`1 mapping, IDbTransaction transaction, Nullable`1 cmdType)
   at SMCM.Data.Repositories.ClienteMedidoreLeituraRepository.Importacao2Async(ClienteMedidorImportacaoModel clienteMedidorImportacao) in C:\_ELFSM\smcm\src\SMCM.Data\Repositories\ClienteMedidoreLeituraRepository.cs:line 224
   at SMCM.Api.Controllers.ClienteMedidoreLeituraController.Importacao2Async(ClienteMedidorImportacaoModel clienteMedidorImportacao) in C:\_ELFSM\smcm\src\SMCM.Api\Controllers\ClienteMedidoreLeituraController.cs:line 94
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

[GuibsonKrause]

The following solution worked for me:
http://www.mukeshkumar.net/articles/csharp/two-ways-to-insert-bulk-data-into-oracle-database-using-csharp