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");
            }

[pabdi]

Surely there is more interest for this to feature to work and be supported then just 1 person/request? Parsing a commo separated string and doing PL/SQL acrobatics vs C# passing a structured data is a no brainer but I could be wrong.

Is the limitation on Dapper, OracleDapper or other sides?

[mgravell]

There are a few things in play here:

1. there's no common ADO.NET API surface for this provider-specific feature, making it hard to generalize or implement without the reference
2. I'm not an Oracle person, so speaking entirely personally, today I don't know quite what the appropriate generated code would look like

2 is solvable by either external input or me finding the time to investigate

1 is trickier, but compound this with the "we don't want to keep emitting IL" problem, and I think this would make a great candidate for the AOT work; the reason I say this is: in the AOT world, we know what the consumer references are, avoiding the reference problem. That just leaves mapping an input scenario to "what code we spit out", and adding a "how we tell Dapper to care". More importantly, by writing it as AOT, if the code is "close but not quite right", it would be easy for anyone to suggest fixes and improvements - something that is very hard and rare in the vanilla codebase.

Assume I'm ignorant of Oracle; is there a good example of this I can look at to understand what we would emit? If it is an interesting demand feature, we can try to do something.