Calling Oracle Stored Procedure from ASP.NET

Question

I'm trying to update a table via a stored procedure from asp.net. I get the following error message: "wrong number or types of arguments in call to 'UPDATE_DIRECT_BILL_DETL_SP'"

I just can't seem to figure out what is wrong. I believe I may not be using the correct Oracle variable types in my Cmd.Parameter.Add statements.

I appreciate any help that can be provided!

Thank you.

Here are the details:

I'm using Oracle.DataAccess.dll (ODP.NET)

Here is my function:

Public Function UpdateDirectBill(ByVal invDetID As Int32, ByVal appNum As String, _
             ByVal lastUpdtByNm As String) As Int32

    Dim numRecUpdated As Int32 = 0

    Cmd.CommandType = CommandType.StoredProcedure
    Cmd.CommandText = "UPDATE_DIRECT_BILL_DETL_SP"

    'These are the values I'm using for the params
    invDetID = 1
    appNum = "3333"
    lastUpdtByNm = "N015058"

    Cmd.Parameters.Add("invDetID", OracleDbType.Int32,    invDetID,   ParameterDirection.Input)
    Cmd.Parameters.Add("appNum",   OracleDbType.Varchar2, appNum,        ParameterDirection.Input)
    Cmd.Parameters.Add("lastUpdtByNm",  OracleDbType.Varchar2, lastUpdtByNm,  ParameterDirection.Input)
    Cmd.Parameters.Add("numRecUpdated", OracleDbType.Int32,    numRecUpdated, ParameterDirection.Output)

    Conn.Open()
    Cmd.ExecuteNonQuery()
    Conn.Close()

    Dim recsUpdated As Int32 = Convert.ToInt32(Me.Cmd.Parameters("numRecUpdated").Value.ToString())

    Return recsUpdated
End Function

My Stored Procedure:

  PROCEDURE UPDATE_DIRECT_BILL_DETL_SP
  (invDetID     IN invoice_detail.invoice_detail_id%TYPE,
   appNum       IN invoice_detail.application_num%TYPE,
   lastUpdateNm IN invoice_detail.last_update_by_nm%TYPE, 
   rowCnt       OUT number)
  IS
  BEGIN 
    UPDATE INVOICE_DETAIL A
    SET
    A.APPLICATION_NUM=appNum,
    A.LAST_UPDATE_BY_NM = lastUpdateNm, 
    A.LAST_UPDATE_BY_DT=SYSDATE
    WHERE A.INVOICE_DETAIL_ID=invDetID;

    rowCnt := SQL%ROWCOUNT;
    if rowCnt = 1 then
      COMMIT;
    ELSE
      ROLLBACK;
    END IF;
  END UPDATE_DIRECT_BILL_DETL_SP;

The columns I'm updating are of the following types:

   invDetID IN invoice_detail.invoice_detail_id%TYPE        of type Number
   appNum in invoice_detail.application_num%TYPE            of type Varchar2(10 byte)
   lastUpdateNm IN invoice_detail.last_update_by_nm%TYPE    of type Varchar2(50 byte)

Please format your code using the { } button.

GolezTrol
– GolezTrol

2011-02-02 15:29:00 +00:00
Commented Feb 2, 2011 at 15:29 — GolezTrol
– GolezTrol, Commented Feb 2, 2011 at 15:29

Richard Friend · Accepted Answer · 2011-02-02 15:31:04Z

1

you define rowCnt in the sproc but use numRecUpdated in your function..

Try

Cmd.Parameters.Add("rowCnt", OracleDbType.Int32,    numRecUpdated, _
    ParameterDirection.Output)

And

Dim recsUpdated As Int32 = _
    Convert.ToInt32(Me.Cmd.Parameters("rowCnt").Value.ToString())

answered Feb 2, 2011 at 15:31

Richard Friend

16k1 gold badge45 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vadim · Accepted Answer · 2011-02-03 03:33:19Z

0

Your parameter names don't match up 'rowCnt' and 'numRecUpdated'. Also you're specifying the type as Int in your call, but the stored proc specifies it as a Number which maps to a Decimal. So you would need to specify it either as a OracleDbType.Decimal type rather than an int. Or change your stored proc parameter to an INTEGER http://download.oracle.com/docs/cd/B19306_01/win.102/b14307/OracleDbTypeEnumerationType.htm

Cmd.Parameters.Add("rowCnt", OracleDbType.Decimal, numRecUpdated, ParameterDirection.Output)

edited Feb 3, 2011 at 3:33

answered Feb 2, 2011 at 15:30

Vadim

18k5 gold badges41 silver badges62 bronze badges

1 Comment

DFlynn Over a year ago

I made those changes, but it still doesn't work. The error message is the same too.

Collectives™ on Stack Overflow

Calling Oracle Stored Procedure from ASP.NET

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related