Calling an Oracle stored procedure in Dapper

Question

I have an application that was calling an Oracle stored procedure by passing a string with the parameters in the string, i.e.

string Query = "CALL MySP(" + Variable1 + ", " + Variable2 + ")";

This worked, or at least the last time I tried it it worked.

I need to rewrite the query using DynamicParameters.

However when I do an Execute(Query, Parms, commandType: CommandType.StoredProcedure), I get errors.

Here is an example of what I tried:

using(OracleConnection conn = new OracleConnection(myConnectionString)
{
    string Query = "CALL MySP(:Var1, :Var2)";
    DynamicParameters parms = new DynamicParameters();

    parms.Add("Var1", Variable1, DbType.String, ParameterDirection.Input);
    parms.Add("Var2", Variable2, DbType.String, ParameterDirection.Input);

    var results = connection.Execute(Query, parms, commandType: CommandType.StoredProcedure);
}

When I run it with the above query, I get this error:

ORA-06550: line 1, column 12:

PLS-00103: Encountered the symbol "MySP" when expecting one of the following:

:= . ( @ % ;
The symbol ":=" was substituted for "MySP" to continue.

If I remove the CALL statement:

Query = "MySP(:Var1, :Var2); 

then I get this error message:

ORA-01008: not all variables bound

Can anyone see what I am doing wrong?

Answer 1

The problem is that I keep looking on the C# and Dapper side of the equation. Our Oracle DBA set the permissions on the Stored Procedure and it ran.

However I could not tell Dapper that the CommandType was Stored Procedure and my string to call the stored procedure had to look like:

string Query = "CALL MySP(:Var1, :Var2)";

If I removed "CALL" I got an error. If I added "commandType: CommandType.StoredProcedure" to the Dapper Execute function I got a binding error.

Our DBA said he was going to look more into it to see what he can find out on his end so that we can call the stored procedure using the CommandType.StoreProcedure parameter in the Execute function.