ExecuteNonQuerySql - set OUT parameter from anonymous SQL block

Question

I have a generic handler I am attempting to have interact with a stored oracle procedure, but this stored procedure uses an out parameter

i am patterning my work after some other files , which essentially uses a listDictionary to pass in parameters to the ExecuteNonQuerySql like this

ListDictionary AddParams = new ListDictionary();
//the In variables, they work fine
          AddParams.Add("type", context.Request["type"]);
          AddParams.Add("idnumber", context.Request["idnumber"]);
// here is the out variable I want
AddParams.Add("o_out", 0);

string sSql = @" begin schema.stored_proc( :type, :idnumber, :o_out) ; end;";
dbi.ExecuteNonQuerySql(sSql, AddParams);

results = "{ \"result\": "+AddParams["o_out"]+" }";

context.Response.Write(results);    

Currently only 0 returns due to that being set initially, it is not being overwritten, i expect the o_out to be a -1 0 or 1 thats what the proc will return

Any suggestions on how i can return a proc out var in this situation?

EDIT:

the ExecuteNonQuerySql is a public shared function of the Database Interface class (posted below, looks like VB)...since i am using a generic handler to access this, maybe theres a way around using listdictioary as the parameter list, which i dont think will alow me to set direction

Public Class DBInterface
...
    Public Shared Function ExecuteNonQuerySql(ByVal sqlStatement As String, _
                                        ByVal cmdType As CommandType, _
                                        ByVal trans As IDbTransaction, _
                                        ByVal parameters As IDictionary) As Integer

        If trans Is Nothing Then Throw New ArgumentNullException("trans")
        Using cmdDyn As New OracleCommand(sqlStatement, DirectCast(trans.Connection, OracleConnection))
            cmdDyn.CommandType = cmdType
            ApplyParameterList(cmdDyn, parameters)
        Return cmdDyn.ExecuteNonQuery()
    End Using
End Function

Answer 1

I am not sure what data layer is that, but in general, you should be able to set param direction. Please see http://msdn.microsoft.com/pl-pl/library/yy6y35y8%28v=vs.110%29.aspx and http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters%28v=vs.110%29.aspx

Edit:

Uh-oh is it vb? :)

I believe you need to create explicit query parameter and set it's direction. The implicit way - just as you have in your code I guess since no ApplyParameterList code is available - always creates "in" parameter. So it should be sth like this:

var outParam=new OracleParameter("o_out",value){Direction=ParameterDirection.Output};

and then pass it to your ExecNonQuerySql method somehow and add to cmdDyn.Parameters:

cmdDyn.Parameters.add(outParam)

then call ExecNonQuery and the param value should be set as expected.