Environment:
- .Net Core v.2.1
- Dapper ORM v.1.50.5
- Oracle ODP.Net Beta:3
Problem:
1. Passing variable and receiving objects from/to Oracle SP:
a. ODP.Net does not seem to provide support for OracleDbTypes like
- NUMBER
- TABLE, etc.
- It throws the error "wrong number of types or arguments". Even when I am passing the exact number of input and output parameters with exact names.
b. The stored procedures on the other side are just receiving datatypes like NUMBER and are returning multiple TABLES as output parameter.
Code:
1. How I am including the parameters in the command:
var sql = OracleStoredProcedures.ValidateZipcode;
var parameters = new OracleDynamicParameters();
parameters.Add("id_sth", OracleDbType.Int32, ParameterDirection.Input, zipcode);
parameters.Add("id_sth2", OracleDbType.Int32, ParameterDirection.Input, zipcode);
parameters.Add("input_str", OracleDbType.Varchar2, ParameterDirection.Input, "random string");
parameters.Add("tdata", OracleDbType.RefCursor, ParameterDirection.Output);
parameters.Add("tdata2", OracleDbType.RefCursor, ParameterDirection.Output);
//parameters.Add("success_ful", OracleDbType.Varchar2, ParameterDirection.Output);
//parameters.Add("error_message", OracleDbType.Varchar2, ParameterDirection.Output);
var reader = SqlMapper.QueryMultiple(_unitOfWork.GetConnection(),
"TEST", parameters, commandType: CommandType.StoredProcedure);
//var success = reader.Read<bool>();
var result = reader.Read<Object>();
var result2 = reader.Read<Object>();
var result3 = reader.Read<string>();
**This code works when the SP is returning type REFCURSOR but it does not work on the SP's that are returning the TABLE on the same exact query.**
I am new to using Oracle with .net any help will be highly appreciated.
Thank you