guys - i cannot figure this one out. i keep getting the "wrong number or types of arguments in call" error & for the life of me can't figure out why. i've got dozens of other programs doing way more difficult stuff than this so, i'm officially stumped.
4 parameters is 4 parameters & varchar2 shouldn't have a problem with LongVarChar which is in the Oracle Client.
any ideas from anyone?
i'll owe u lunch
thanks again
rik
create or replace procedure myNaturalAccount(v_gloviaAccount varchar2,
create or replace procedure myNaturalAccount(v_gloviaAccount varchar2,
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()