locked
odp.net wrong number or types of arguments in call RRS feed

  • Question

  • User891724343 posted

    All,

      We've upgraded our website to VS2015 and using ODP.net which uses the Oracle 4.0 dll.  I'm getting an error with wrong number of types of arguments and everything looks fine. I'm hoping someone here can see my code below and figure out what's wrong.

    DB:

    TYPE T_CURSOR IS REF CURSOR;

    PROCEDURE GET_REQUESTS_SRCH (P_USR_ID_NUM IN REQUEST.REQUESTOR_ID%TYPE,

            P_ROLE_NAME IN VARCHAR,

           P_REQUEST_ID IN REQUEST.REQ_ID%TYPE,

                                P_REQ_TITLE IN REQUEST.REQ_TITLE%TYPE,

                                P_STARTDATE IN VARCHAR,

                                P_ENDDATE IN VARCHAR,

                                P_REQUESTOR_ID IN REQUEST.REQUESTOR_ID%TYPE,

                                P_REQ_STATUS_ID IN REQUEST.WFLOW_STATUS_ID%TYPE,

                                P_NSMCODEorTITLE IN VARCHAR,

                                P_NSM_LEVEL IN ORG_REQ.ORG_LEVEL_ID%TYPE, 

                                CUR_SEARCH_REQ_RESULTS OUT T_CURSOR,

                                P_OUT OUT NUMBER,

                                P_MSG OUT VARCHAR)

    .net code:

     dpDataParamArr = New ArrayList

            'User's User_ID needed for Security

            dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, STP_Constants.P_USR_ID_NUM, "INT32", objUser.UserIdNumber, ParameterDirection.Input))

            'Optional? This was needed until an error was discovered after Rnd 2 of testing

            dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_ROLE_NAME", "VARCHAR", strRole, ParameterDirection.Input))

     

            'If no Request # was entered, must be set to "-1" for query to work

            If intRequestId = "" Then

                dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_REQUEST_ID", "INT32", -1, ParameterDirection.Input))

            Else

                dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_REQUEST_ID", "INT32", intRequestId, ParameterDirection.Input))

            End If

            'If no Request Title was entered, must set to "-1" for query to work

            If strReqTitle = "" Then

                dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_REQ_TITLE", "VARCHAR", -1, ParameterDirection.Input))

            Else

                dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_REQ_TITLE", "VARCHAR", strReqTitle, ParameterDirection.Input))

            End If

            'If no StartDate was entered, must set to "-1" for query to work

            If strStartDate = "" Then

                dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_STARTDATE", "VARCHAR", -1, ParameterDirection.Input))

            Else

                dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_STARTDATE", "VARCHAR", strStartDate, ParameterDirection.Input))

            End If

            'If no EndDate was entered, must set to "-1" for query to work

            If strEndDate = "" Then

                dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_ENDDATE", "VARCHAR", -1, ParameterDirection.Input))

            Else

                dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_ENDDATE", "VARCHAR", strEndDate, ParameterDirection.Input))

            End If

     

            'Requestor ID defaults to "-1" in dropdown if nothing is selected

            dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_REQUESTOR_ID", "INT32", intRequestorId, ParameterDirection.Input))

     

            'Request Status defaults to "-1" in dropdown if nothing is selected

            dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_REQ_STATUS_ID", "INT32", intRequestStatus, ParameterDirection.Input))

     

            'NEW CODE TO SEARCH BY CODE 4/6/2006 EDGAR P_NSMCODEorTITLE

            dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_NSMCODEorTITLE", "VARCHAR", strTitleOrCode.Trim, ParameterDirection.Input))

            'END NEW

            'JT 2.1 Add NSM Level defaults to "-1" in dropdown if nothing is selected

            dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, "P_NSM_LEVEL", "INT32", intNSMLevel, ParameterDirection.Input))

     

            dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, STP_Constants.CUR_SEARCH_REQ_RESULTS, "cursor", DBNull.Value, ParameterDirection.Output))

            dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, STP_Constants.P_OUT, "INT32", 0, ParameterDirection.Output))

            dpDataParamArr.Add(New DataParameter(STP_Constants.GET_REQUESTS_SRCH, STP_Constants.P_MSG, "varchar", DBNull.Value, ParameterDirection.Output))

     

            GetRequestsSrch = GetDataSet(dpDataParamArr, STP_Constants.PKG_SEARCHES, STP_Constants.GET_REQUESTS_SRCH)

    Thanks,

    JT

    Thursday, December 14, 2017 5:30 PM

All replies

  • User269602965 posted

    When passing NUMBERS to Oracle NUMBER datatype, I have had better luck on .NET side using DECIMAL data type and not INT32

    Thursday, December 14, 2017 6:00 PM
  • User891724343 posted

    Thanks I'll try that but I think the issue is with the cursor.

    Thursday, December 14, 2017 6:22 PM
  • User269602965 posted

    That is true... I not ever tried passing .NET vars to table column type names.  just p1 IN VARCHAR, p2 IN NUMBER, standard data types.

    Thursday, December 14, 2017 8:43 PM
  • User269602965 posted

    Did you check.. .NET add parameters must be in same order as the PL/SQL call?

    Friday, December 15, 2017 9:30 PM