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

Repro:

var param = new OracleDynamicParameters();
param.Add("Foo", "Bar");
//call connection.Execute or other Dapper method with param as parameters

var p = param.GetParameter("Foo") // this line throws exception

Can you provide an example to Run Stored Procedure With Array As Parameter and return RefCursor?

Tried by example RunStoredProcedureWithArrayAsParameters but this is what I get:
PLS-00306: wrong number or types of arguments in call to procedure

parameters.Add("IN_VAR1", Var1.ToArray(), OracleMappingType.Varchar2, ParameterDirection.Input);
parameters.Add("OUT_REFCURSOR", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
parameters.ArrayBindCount = Var1.Count();

var r = Connection.QueryAsync<Customer>(qry, parameters, commandType: CommandType.StoredProcedure);

//sql
create TYPE ARRAY_TYPE IS TABLE OF VARCHAR2(1000);

PROCEDURE my_procedure(
    IN_VAR1 IN    ARRAY_TYPE DEFAULT NULL,
   OUT_REFCURSOR  OUT   SYS_REFCURSOR
)
...

Hello There!

First of all, thank you very much for the library.

I have found the following problem in file Dapper.Oracle/BulkSql/BulkOperation.cs:

line 79: var dbType = map.DbType ?? OracleMapper.GuessType(obj.First().GetType());

In the above line OracleMapper.GuessType(obj.First().GetType()) does not work as expected, because obj.First().GetType() returns generic class type in place of property type. I think, it should be changed from "obj.First().GetType()" to "obj.Select(map.Property).ToArray().First().GetType()" which returns the required property type.

Thanks & Regards

We need some CI and commits, so Appveyor is the best choice for Windows builds.

My company runs the Microsoft Security Code Analysis toolset to audit our managed and unmanaged code. BinSkim is one of those tools and reports the following issue with the Dapper.Oracle Nuget package:

BinSkim error BA2022: Images should be correctly signed by trusted publishers using cryptographically secure signature algorithms.

Error details:

Images should be correctly signed by trusted publishers using cryptographically secure signature algorithms. This rule invokes WinTrustVerify to validate that binary hash, signing and public key algorithms are secure and, where configurable, that key sizes meet acceptable size thresholds.

Cryptographically weak signatures: [digest algorithm: 'sha1NoSign' + digest encryption algorithm: 'RSA']

Could you please sign your package using SHA-2 signatures?

I am trying out Dapper.Oracle in a large project and currently experimenting with sys ref cursor as return type from an Oracle function. Our dba says that the cursors are not closed and keeps hanging after each call to .QueryAsync(...).
Eventually Oracle will be hogged with orphaned cursors until the connection is closed. I am using .Net 6, DI container (IServiceColletion) etc.

Do you know a way to make Dapper.Oracle or Dapper to close the cursor once the call has been made and the result returned?

Hi,
I got System.NullReferenceException while I was trying to read PLSQLAssociativeArray output parameter.

in line 63, we will have System.NullReferenceException if valueType.GetProperty("IsNull", typeof(bool)) is null
var isNull = (bool)valueType.GetProperty("IsNull", typeof(bool))?.GetValue(value);

I discovered this when I tried to get value from OracleString[]
This was working in previous version

Hi,
I am struggling with something not sure why not able to execute.

I have a clause in that I would like to execute in a second query...
for simplicity let's say something like :

`

begin

  open :c_query1 for
    select * from T1;

  open :c_query2 for
  select * from T2 t_t2 where t_t2.name in :pNames;

end;

`

where I would have my parameters as
`

            var dynParams = new OracleDynamicParameters();
            dynParams.Add(":c_query1", direction: ParameterDirection.Output, dbType: OracleMappingType.RefCursor);
            dynParams.Add(":c_query2", direction: ParameterDirection.Output, dbType: OracleMappingType.RefCursor);
            dynParams.Add(":pNames", new[] {"AA", "BB"});

`

when I execute this my "in clause" comes empty....
any idea?
thanks

I downloaded the latest copy of the solution. 14 of the 42 tests are failing, Including the DateTime test which I believe is causing my other open issue.

Conversion exception thrown when calling OracleDynamicParameters.Get() on an output parameter with a NUMBER (OracleDecimal) db type.

var params = new OracleDynamicParameters();
...
params.Add(name: "paramName", dbType: OracleMappingType.Int32, direction: ParameterDirection.Output);
... execute non-query ...
var output = params.Get<int>("paramName"); // throws an Exception.

Calling params.Get<decimal>() worked fine but is clunky to then have to convert to an int.

Below I am returning an object with a DateTime object in it.

object:

 public class MyObject
{
    public DateTime CreatedDate { get; set; }
}

Repository method:

 public IEnumerable<MessageBroadcast> GetLatest(MyRequest request)
    {

        try
        {
            using (IDbConnection connection = Connection)
            {

                var parameters = new OracleDynamicParameters();
                parameters.Add("OutOutputData", null, OracleMappingType.RefCursor, ParameterDirection.Output);
                parameters.Add("userid", request.UserId, OracleMappingType.Varchar2, ParameterDirection.Input);
                parameters.Add("count", request.Count, OracleMappingType.Decimal, ParameterDirection.Input);
                return connection.Query<MessageBroadcast>("MyStoredProcedure", parameters, commandType: CommandType.StoredProcedure);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }

Test Method

[Fact]
    public void MyMethod_ShouldReturnLatest()
    {
        //Arrange
        var request = new MyRequest
        {
            UserId = "12345",
            Count = 5

        };

        var expected = new MyObject
        {
           CreatedDate = new DateTime(2019, 02, 06, 04, 05, 07),
        };

        //Act
        var actual = _sut.GetLatest(request);

        //Assert

        var actualSingle = actual.OrderBy(x => x.CompanyId).First();
        actualSingle.CreatedDate.ShouldBe(expected.CreatedDate);

    }

The date I get back is 1/1/0001 00:00:00 Instead of the expected date.

Hi,
first of all - great job. I have been using your libraries for a few weeks and it works very well!

I have noticed that it is not possible to get OracleDynamicParameters values before execution.
Having logged DB procedure name and parameter value is extremely important for debugging.
Very often we have on production that DB procedure did not returned any value or exception and we need parameter value as an input to reproduce the issue.

I have a solution for this, and I will happily fix it if you allow me to. It is minor code change that you can easily review and approve.

Regards
Ogi

This test case throws an exception.

public async Task BulkNotWorking()
 {
     var data = new List<Scenario>()
     {
         new Scenario { Id = -3, Name = "test1", PeriodYear = 2023,  },
         new Scenario { Id = -4, Name = "test1", PeriodYear = 2023,  },
     };

     var query = @"insert into XXDM.ST_SCENARIO (ID, NAME, PERIOD_YEAR)
                   values (:ID, :NAME, :PERIOD_YEAR)";
     var mapping = new BulkMapping<Scenario>[]
     {
         new BulkMapping<Scenario>("ID", x => x.Id.Value),
         new BulkMapping<Scenario>("NAME", x => x.Name),
         new BulkMapping<Scenario>("PERIOD_YEAR", x => x.PeriodYear),
     };

     using var connection = client.GetConnection();
     await connection.SqlBulkAsync(query, data, mapping).ConfigureAwait(false);
 }

If I add appropriate OracleMappingType.xxxx to each of the mappings, like this, it works:

new BulkMapping("ID", x => x.Id.Value, OracleMappingType.Int32),

The exception is:

System.ArgumentException: Value does not fall within the expected range.

Stack Trace: 
OracleParameter.set_Value(Object value)
OracleDynamicParameters.AddParameters(IDbCommand command, Identity identity)
IDynamicParameters.AddParameters(IDbCommand command, Identity identity)
<>c__DisplayClass165_0.b__0(IDbCommand cmd, Object obj) line 1738
CommandDefinition.SetupCommand(IDbConnection cnn, Action2 paramReader) line 128 SqlMapper.TrySetupAsyncCommand(CommandDefinition command, IDbConnection cnn, Action2 paramReader) line 396
SqlMapper.ExecuteImplAsync(IDbConnection cnn, CommandDefinition command, Object param) line 642
BulkOperation.SqlBulkAsync[T](IDbConnection connection, String sql, IEnumerable1 objects, IEnumerable1 mapping, Nullable`1 cmdType, IDbTransaction transaction)
OracleScenarioRepository.BulkNotWorking() line 114
BulkTester.TestBulkInsert() line 18
ThreadOperations.ExecuteWithAbortSafety(Action action)

Finally, this issue was mentioned in https://github.com/DIPSAS/Dapper.Oracle/issues/49, but no suggestions or resolution was provided.

Hi,

Is it possible to have a signed assembly version of this library ? Dapper has a Dapper.StrongName.

object reflected = valueProperty.GetValue(val); return (T)reflected;
//while T type is not the same as reflected, it throw System.InvalidCastException ,see the photo, decimal -->int

return (T)System.Convert.ChangeType(reflected, typeof(T)); //This is OK

When consuming nuget package version 1.0.0 for netstandard or netcore project an error is presented, claiming that the package is not compatible with .netstandard 2.0

I noticed that OracleParameter has a attribute, "UdtTypeName", which allows the user to specify the UdtType that this object is being mapped to in Oracle DB.

This would be helpful as it would allow for a more direct translation from OracleParameter to the object in OracleDynamicParameters.Add(object item) instead of this attribute being clipped off.

My platform is .Net core 3.1
and using ODP.NET CORE library.
I created a function on Oracle 11c and work fine.
Function like :

CREATE OR REPLACE FUNCTION Get_Clob_Content(IDs IN varchar2) 
return TBL_OBJ_COLL 
as
	v_tbl  TBL_OBJ_COLL:=TBL_OBJ_COLL();
	v_sql   varchar2(3000); 

create type TBL_OBJ is object(CONTENT  VARCHAR2(4000),OFFSET  int,MOD   int,FILE_CONTENT_LENGTH   int,INTERATION_COUNT   int);
create type TBL_OBJ_COLL is table of TBL_OBJ;

When I try to use Dapper.net(v2.0.35) to get the entity from the function. And got the error(s)
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00902:Invalid datatype error
`

at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
  at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
  at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
  at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
  at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 419

plain SQL text is working!
select CONTENT from table( CAST(Get_Clob_Content('20010044_01') AS TBL_OBJ_COLL))

C# code:
string sql=@$"select CONTENT as CONTENT from table( CAST(Get_Clob_Content('{tableName}','{colName}','{condition}','{id}') AS TBL_OBJ_COLL)) ";

Can Table() function map to entity?
Is it a bug from ODO.NET Core?Any ideas? Thank you in advance.

Hi Thanks for the excellent library, there is a missing OracleMappingType. Oracle.ManagedDataAccess have a Boolean value that is not present as a mapping type.

Product is licensed under MIT license, but with wrong Organization as copyright holder.
At current, license reads

Copyright 2018 DIPS Community

Correct value should be

Copyright 2020 DIPS AS

This is a similar case to issue #36 except that I would like to get a list of parameters (OracleParameterInfo) and not just the parameter values prior to execution.

foreach (var name in params.ParameterNames)
{
	var p = params.GetParameter(name);
	...
}

Use case: Log all parameter names, dbtypes, and values prior to executing the sql command/query.
Use case: Modify individual parameters prior to execution.

I have an odd use case of writing a temp OracleBlob prior to executing a stored procedure that uses the result of the Blob ChunkWrite as the value of an input parameter.

Hi,
I was wondering if something like this it is supported using OracleDynamicParameters:

string sql = "SELECT * FROM xxx WHERE id IN :ids"
var results = conn.Query(sql, new { ids = new[] { "A", "B", "C", "D", "E" }});

Hi,

We are using Dapper.Oracle, and are sometimes getting InvalidCastException errors when reading decimal values from Oracle, if these values contain too many decimals.

This is a known problem in the Oracle .NET driver. Oracle has provided a way to avoid it : the SuppressGetDecimalInvalidCastException property in the OracleDataReader class that has to be set to true to round the numbers just enough so that they can be read by .NET. However, Dapper does not allow direct access to the data readers, and so we cannot set this property while using Dapper.

Would there be a way to do this with Dapper.Oracle ? Would it be possible to add such a feature ?

as Title says , can't find it

We need to package and ship to NuGet.org to share the goodness!

We need support for .net 5.x

Would it be possible to Upgrade to Net 3.1 at the minimum or add 5.0 too? I didn't have any issues with it work so I don't expect too many code changes would be needed.

Hi

Many thanks for this library!

Can you please document the optional parameter size on OracleDynamicParameters.Add()? The code was not obvious to me. Perhaps it is evident if you have more Oracle knowledge than I have.

Also what happens when you guess a size of 4000 but the result actually is larger?

While it may sound like a random parameter to document, I think these aspects are crucial to understand in order to be able to use this library.

many thanks