rik butcher

rik butcher

  • NA
  • 1
  • 0

Call Oracle Store procedure

Apr 12 2012 1:23 PM
guys - i'm so stumped and almost embarrassed on this one.
i keep getting the dreaded "wrong number or types in call" error. i work in the back-end of oracle all the time as well as the UI side of it & i know there's nothing wrong w/ the oracle procedure. some how someway .net is thinking i've got the wrong DataType or wrong number of parameters & of course the error message doesn't point to any thing special. if anyone can give me any help, it's appreciated.
below is the oracle procedure - very simple as well as the function call from vb.net - again,simple & i've tried every variation on the datatypes w/ the same results.
i even copied the formatting of the .net call from other 'working' procedures that do the same thing. i've run this from the back ground - no problem.
any ideas?
thanks
rik


create or replace procedure myNaturalAccount(v_gloviaAccount varchar2,
   v_acountName varchar2,
   v_discountAccount varchar2,
   return_value out int)
is
   n_discountType int;
begin
  if v_gloviaAccount = v_discountAccount then
   
     select (max(discounttypeid) + 1) into n_discountType from naturalaccount n;
  else
  
     select n.discounttypeid into n_discountType
     from naturalaccount n
     where n.naturalaccountcode=v_discountAccount;
  end if;
 
  insert into naturalaccount
     (naturalaccountcode, naturalaccountname, discounttypeid)
     values (v_gloviaAccount, v_acountName, n_discountType);
  
  commit;
  return_value := 1;

  exception
     when others then
        declare
           n_sqlcode int := sqlcode;
           v_sqlerrm varchar2(4000) := sqlerrm;
           n_errorid int;
        begin
           rollback;  -- optional step
           n_errorid := gold_ws_diagnostic.diag_customerrorcode(n_SQLCODE, v_sqlerrm);
           return_value := n_errorid;
        end;
end myNaturalAccount;


AND THE call from vb.net (asp.net) using system.Data.OracleClienT


Public Function NaturalAccount(ByVal v_gloviaAccount As String, ByVal v_accountName As String, ByVal v_discountAccount As String) As String
        Dim myReturn As Integer = 0
        Dim cn As OracleClient.OracleConnection
        cn = New OracleConnection("Data Source=GOLDTEST.WORLD;User Id=NEWTRACK;Password=newtrack")
       Try
            cn.Open()
            Dim objCommand As New OracleCommand("myNaturalAccount", cn)
            objCommand.CommandType = CommandType.StoredProcedure
            With objCommand.Parameters
                Dim par As OracleClient.OracleParameter
                par = New OracleClient.OracleParameter("v_gloviaAccount", OracleClient.OracleType.VarChar)
                .Add(par)
                par.Direction = ParameterDirection.Input
                If (v_gloviaAccount Is Nothing) Or (v_gloviaAccount = String.Empty) Then par.Size = 1
                If (v_gloviaAccount Is Nothing) Then par.Value = System.DBNull.Value Else par.Value = v_gloviaAccount
                par = New OracleClient.OracleParameter("v_accountName", OracleClient.OracleType.VarChar)
                .Add(par)
                par.Direction = ParameterDirection.Input
                If (v_accountName Is Nothing) Or (v_accountName = String.Empty) Then par.Size = 1
                If (v_accountName Is Nothing) Then par.Value = System.DBNull.Value Else par.Value = v_accountName
                par = New OracleClient.OracleParameter("v_discountAccount", OracleClient.OracleType.VarChar)
                .Add(par)
                par.Direction = ParameterDirection.Input
                If (v_discountAccount Is Nothing) Or (v_discountAccount = String.Empty) Then par.Size = 1
                If (v_discountAccount Is Nothing) Then par.Value = System.DBNull.Value Else par.Value = v_discountAccount
                par = New OracleClient.OracleParameter("return_value", OracleClient.OracleType.Int32)
                .Add(par)
                par.Direction = ParameterDirection.Output
            End With
            objCommand.ExecuteNonQuery()
            myReturn = CInt(objCommand.Parameters("return_value").Value())
        Catch ex As Exception
            If (Not cn Is Nothing) Then cn.Close()
            Dim myError As String = ex.Message
            Return myError
        Finally
            cn.Close()
        End Try
        Return myReturn
    End Function