How to get value from procedure with dapper OracleDynamicParameters()?

Question

In my application, I've installed Dapper from nuget package in .net core project. I run a procedure. I see in debug mode that the parameter receives the value, but I can't read this value.

public decimal AddMaster(MaterialMaster req)
{
    decimal id = 0;
    try
    {
        var dynamicParam = new OracleDynamicParameters();
        var conn = this.GetConnection();
        if (conn.State == ConnectionState.Closed) 
        { conn.Open(); }
        if (conn.State == ConnectionState.Open)
        {
            dynamicParam.Add("P_CAL_ID", OracleDbType.Decimal, ParameterDirection.InputOutput);
            dynamicParam.Add("P_MATERIAL_KOD", OracleDbType.Varchar2, ParameterDirection.Input, req.MATERIAL);
            dynamicParam.Add("P_SEZON_KOD", OracleDbType.Varchar2, ParameterDirection.Input, req.SEASON);
            dynamicParam.Add("P_CURRENCY_ID", OracleDbType.Decimal, ParameterDirection.Input,0);
            dynamicParam.Add("P_FABRIKA_KOD", OracleDbType.Varchar2, ParameterDirection.Input, req.PLANT_ID);
            dynamicParam.Add("P_STATUS", OracleDbType.Decimal, ParameterDirection.Input,1);
            dynamicParam.Add("P_EKLEYEN_KULLANICI", OracleDbType.Decimal, ParameterDirection.Input, 60950);
            dynamicParam.Add("P_EKLENEN_TARIH", OracleDbType.Date, ParameterDirection.Input, new DateTime());
            dynamicParam.Add("P_GUNCELLEYEN_KULLANICI", OracleDbType.Decimal, ParameterDirection.Input, 60950);
            dynamicParam.Add("P_GUNCELLENEN_TARIH", OracleDbType.Date, ParameterDirection.Input, new DateTime());
            dynamicParam.Add("P_CUSTOMER_ID", OracleDbType.Decimal, ParameterDirection.Input, req.CUSTOMER_ID);
            var x = SqlMapper.Query(conn, "BMS.PA_CALCULATION_TOOL.INS_CAL_COST_CAL_MASTER", param: dynamicParam, commandType: CommandType.StoredProcedure).SingleOrDefault();
            id = dynamicParam.Get<OracleDecimal>("P_CAL_ID");//<==This line throws error
        }
        return id;
    }
    catch(Exception ex)
    {
        return id;
    }
}

id = dynamicParam.Get<OracleDecimal>("P_CAL_ID");

Above line throws an error.

There is no Get method found as error.

How can I fix this error. I can't reach P_CAL_ID.

Answer 1

I never used Dapper with Oracle, but I can see following improvements in your code.

As mentioned here, Get is generic method.

public T Get<T>(string name)

So, may be you should read it like below:

OracleDecimal oraid = dynamicParam.Get<OracleDecimal>("P_CAL_ID");

and then convert it to your native decimal type.

I am not sure but may be you need to change your parameter direction to Output:

dynamicParam.Add("P_CAL_ID", OracleDbType.Decimal, ParameterDirection.Output);