Calling store producer in oracle database 12c I get invalid SQL statement

[fadl8]

Hi there i have store store producer in oracle database 12c with params in and out and when i run the code i get error the SQL is not invalid the code below

using (OracleConnection connection = new OracleConnection(_connection.ConnectionString))
{  
    // Create a OracleDynamicParameters object.
    var parameters = new OracleDynamicParameters();
    // Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = null, int? size = null)  
  
    parameters.Add("in_RQ_SALES_REQUEST", OracleDbType.Int32 ,ParameterDirection.Input , 14478);
    parameters.Add("in_RQ_AMOUNT", OracleDbType.Int32 ,ParameterDirection.Input , 2500);
    parameters.Add("in_RQ_CURRENCY", OracleDbType.Char ,ParameterDirection.Input ,"YER", 3);
    parameters.Add("in_RQ_TP_CHANNEL", OracleDbType.Char ,ParameterDirection.Input ,"APP", 3);
    parameters.Add("in_RQ_ACC_TYPE", OracleDbType.Varchar2 ,ParameterDirection.Input ,"SUB"); 
    parameters.Add("in_RQ_MAIN_SRVC_ID", OracleDbType.Int32 ,ParameterDirection.Input ,1);
    parameters.Add("in_RQ_SUB_SRVC_ID", OracleDbType.Int32 ,ParameterDirection.Input ,1); 

    parameters.Add("in_RQ_INTERNAL_EXTRA1", OracleDbType.Varchar2 ,ParameterDirection.Input ,"123456"); 
    parameters.Add("in_RQ_INTERNAL_EXTRA2", OracleDbType.Varchar2 ,ParameterDirection.Input ,"775550902"); 

    
    // Add the out parameters to the OracleDynamicParameters object.
    parameters.Add("P_OUT_RESULT", OracleDbType.Int32, ParameterDirection.Output);
    parameters.Add("P_OUT_RESULT_DESC", OracleDbType.Varchar2, ParameterDirection.Output);

    // Call the stored procedure.
    var result = connection.Execute("SP_INIT_SALES_REQUEST", parameters);

    // Retrieve the out parameter values from the OracleDynamicParameters object.
    // int outResult = parameters["out_Result"].Value<int>();
    // string outResultDesc = parameters["out_Result_Desc"].Value<string>();

    // Close the connection.
    connection.Close();

    return Ok("result =>" + result.ToString());
}

And i can not get the out params

// Retrieve the out parameter values from the OracleDynamicParameters object.
// int outResult = parameters["out_Result"].Value<int>();
// string outResultDesc = parameters["out_Result_Desc"].Value<string>();

[mgravell]

Add commandType: CommandType.StoredProcedure) to the Execute.

I have been toying with adding this automatically if it isn't specified and the SQL does not contain any whitespace characters. I cannot think of any scenario where that would be wrong. Thoughts?

[mgravell]

Also parameters.GetValue<int>(name) - I've also been toying with a simpler API for this!

[fadl8]

thank you @mgravell

actually I edit the code like that and its worked but i have issue I can not get the out params can you help my pls

using (OracleConnection con = new OracleConnection(_connection.ConnectionString)){
            OracleCommand cmd = new OracleCommand("SP_INIT_SALES_REQUEST",con);
            
            cmd.InitialLONGFetchSize = 1000;
            cmd.CommandType = CommandType.StoredProcedure;
            // OracleParameter resultNo = new OracleParameter("@OUT_RESULT", ParameterDirection.Output);
            // OracleParameter des = new OracleParameter("@OUT_RESULT_DESC", ParameterDirection.Output);
            int resultNo = 100;
            string des = string.Empty;
            cmd.Parameters.Add("@IN_RQ_SALES_REQUEST", 99399);
            cmd.Parameters.Add("@IN_RQ_AMOUNT", 2000);
            cmd.Parameters.Add("@IN_RQ_CURRENCY", "YER");
            cmd.Parameters.Add("@IN_RQ_TP_CHANNEL", "APP");
            cmd.Parameters.Add("@IN_RQ_ACC_TYPE", "SUB");
            cmd.Parameters.Add("@IN_RQ_MAIN_SRVC_ID", 1);
            cmd.Parameters.Add("@IN_RQ_SUB_SRVC_ID", 1);
            cmd.Parameters.Add("@IN_RQ_INTERNAL_EXTRA1", "123456");
            cmd.Parameters.Add("@IN_RQ_INTERNAL_EXTRA2", "775550902");
            
            cmd.Parameters.Add("@OUT_RESULT", resultNo);
            cmd.Parameters.Add("@OUT_RESULT_DESC", des);
              
            con.Open(); 
            // Get the output values from the stored procedure
            var result = await cmd.ExecuteReaderAsync(); 
            con.Close();
            return  Ok("resultNo => " + " des =>");
            } 

[fadl8]

@mgravell when i add cmd.parameters.GetValue<int>(name) i get error i think the var parameters = new OracleDynamicParameters(); Do not has this method .GetValue<int>(name)

and the is the error massage OracleCommand' does not contain a definition for 'parameters' and no accessible extension method 'parameters' accepting a first argument of type 'OracleCommand' could be found (are you missing a using directive or an assembly reference?)

[mgravell]

Ah, right. I didn't notice the OracleDynamicParameters - I assumed you were using Dapper. That type is not something that Dapper provides, so: for guidance on that you'd need to ask whoever wrote that. I can only offer guidance on the Dapper API.