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