0

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
Jay Rizzi
  • 4,196
  • 5
  • 42
  • 71
  • just check executing the stored proc manually in oracle passing same parameter and see what it is returning... it may be a stroed proc problem ....just to be sure .. – Pranav Dec 05 '14 at 15:40
  • stored proc works fine, if i can figure out how to bind the out variable i'll be all squared away – Jay Rizzi Dec 05 '14 at 15:43
  • First, why do you need to do it kinky way. You can execute it simply by using `ExecuteNonQuerySql`, call your `schema.stored_proc` and add parameters to command object. This is better because DbEngine not need to compile anonymous block-SP already compiled. NOW... **about output parameters** - 4 rules: **1** - set parameterDirection explicitly. **2** - Set correct data type. **3** - for string, set correct size or instead of "abc" you may get only "a" because default is 1. **4** - place them in correct order unless you set MapByName flag - in this case provide correct names.It will work fine – T.S. Dec 06 '14 at 05:13
  • I think i need to do it this way because i can only write generic handlers, i am not allowed to change the compiled .net 3rd party code, i have to work with what they give me – Jay Rizzi Dec 08 '14 at 15:05

1 Answers1

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.

cyberhubert
  • 203
  • 1
  • 9