locked
ORA-06550 PLS-00306: wrong number or types of arguments in call to [PROCEDURE] RRS feed

  • Question

  • User-495795224 posted

    I am attempting to update a VB.net website that currently uses System.Data.OracleClient. Every now and then I run into something funky that doesn't make much sense to me and I assume it has something to do with the outdated client, but I wanted to post here and get more eyes on it to see what I might be overlooking or missing.

    I am attempting to create a new budget for a contract. When a user first requests a new budget to be created a new grant id is created, then the budget item is put together with some default values and a record is created in the database... at least that's what is supposed to happen.

    The following is a snippet from the link button on the web page that initiates the code that runs the call to the database. I've cut some irrelevant things out to shorten the snippet:

    Private Sub lnkAddBudg_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lnkAddBudg.Click
    
        '... variables omitted'
        Dim lBudgetArea As New BudgetArea
        Dim intNewContrBudgID As Integer
            
        Try
            'Add new budget record to database with default values'
            lBudgetArea.ContractBudgetID = 0
            lBudgetArea.ContractBudgetDescr = "New budget"
            lBudgetArea.MaxAmount = 0
            lBudgetArea.GrantID = CInt(Session("GrantID"))
    
    	intNewContrBudgID = AdminLib.AddBudgetArea(lBudgetArea)
    
            '... code for processing and binding to web controls'
    
        Catch ex As Exception
            '.. error handling code'
    
        End Try
    
    End Sub

    Here is the code that actually makes the call to the database procedure. The code fails on the call to .ExecuteNonQuery()

    Public Shared Function AddGrantBudgetArea(ByVal vBudgetArea As BudgetArea) As Integer
    
        Dim OracleCon As New OracleConnection(ConfigurationManager.AppSettings("BudgetConnectionString"))
        Dim myCmd As New OracleCommand
        Dim intNewContrBudgID As Integer
    
        Try
            OracleCon.Open()
    
            With myCmd
    
                .Connection = OracleCon
                .CommandType = CommandType.StoredProcedure
                .CommandText = ConfigurationManager.AppSettings("BudgetSchema") & ".pkg_GRANTS.proc_ADD_BUDGET"
    
                .Parameters.Add("Grant_ID_in", OracleType.Int32, 10).Value = HndlNull.IntNum(vBudgetArea.GrantID)
                .Parameters.Add("Contract_ID_in", OracleType.Int32, 10).Value = 0
                .Parameters.Add("Max_Amount_in", OracleType.Double).Value = HndlNull.DblNum(vBudgetArea.MaxAmount)
                .Parameters.Add("Contract_Budg_Descr_in", OracleType.VarChar).Value = HndlNull.Str(vBudgetArea.ContractBudgetDescr)
                Dim pResults As New OracleParameter("Contract_Budget_ID_out", OracleType.Int32, 10)
                pResults.Direction = ParameterDirection.Output
                .Parameters.Add(pResults)
    
                .ExecuteNonQuery()
    
                intNewContrBudgID = CInt(pResults.Value)
    
            End With
    
            Return intNewContrBudgID
    
        Catch ex As Exception
            Throw ex
    
        Finally
            If (Not OracleCon Is Nothing) Then
                OracleCon.Close()
                OracleCon.Dispose()
                myCmd.Dispose()
            End If
    
        End Try
    
    End Function

    Finally; here is the Oracle Procedure (PL/SQL) that is executed on the DB:

    /* ---- Add a contract budget record.  --------------------*/
    
    PROCEDURE proc_ADD_BUDGET(
      Grant_ID_in              IN CONTRACT_BUDGETS.GRANT_ID%TYPE,
      Contract_ID_in           IN CONTRACT_BUDGETS.CONTRACT_ID%TYPE,
      Max_Amount_in            IN CONTRACT_BUDGETS.MAX_AMOUNT%TYPE,
      Contract_Budget_Descr_in IN CONTRACT_BUDGETS.CONTRACT_BUDGET_DESCR%TYPE,
      Contract_Budget_ID_out   OUT NUMBER) IS
    
      v_ContractBudgID NUMBER(10);
    
       BEGIN
    
        IF Contract_ID_in > 0 THEN
    
            INSERT INTO CONTRACT_BUDGETS(
               Grant_ID,
         Contract_ID,
            Contract_Budget_ID,
         Max_Amount,
         Contract_Budget_Descr)
         VALUES(NULL,
                Contract_ID_in,
                SEQ_GRANT_BUDG_ID.NEXTVAL,
             Max_Amount_in,
             Contract_Budget_Descr_in);
        ELSE
    
            INSERT INTO CONTRACT_BUDGETS(
               Grant_ID,
         Contract_ID,
         Contract_Budget_ID,
         Max_Amount,
         Contract_Budget_Descr)
         VALUES(Grant_ID_in,
                NULL,
                SEQ_GRANT_BUDG_ID.NEXTVAL,
             Max_Amount_in,
             Contract_Budget_Descr_in);
    
        END IF;
    
        SELECT SEQ_GRANT_BUDG_ID.CURRVAL
       INTO v_ContractBudgID
       FROM Dual;
    
        Contract_Budget_ID_out := v_ContractBudgID;
    
       END proc_ADD_BUDGET;


    The error I am receiving is

    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'PROC_ADD_BUDGET'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    ???? I appreciate any assistance with this issue

    Monday, October 29, 2012 3:40 PM

Answers

  • User269602965 posted

    I would specify parameter direction for your IN variables as well as OUT

    And, as illogical as it sounds, I have had this suggestion fix problems for me at times as well.

    For the Oracle Column Object with datatype NUMBER(x,0) which is an integer by definition,

    use .Decimal data type in your .NET parameter definition instead of Int32.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 30, 2012 6:10 PM

All replies

  • User477186420 posted

    hey

    provide structure of "CONTRACT_BUDGETS" tablw with data type.

    let me know if any query

    Tuesday, October 30, 2012 9:16 AM
  • User-495795224 posted

    Here is the table

    CONTRACT_BUDGETS TABLE
    COLUMN NAME DATA TYPE NULLABLE DATA DEFAULT
    CONTRACT_BUDGET_ID NUMBER (10,0) No (null)
    MAX_AMOUNT NUMBER (11,2) Yes 0
    CONTRACT_BUDGET_DESCR VARCHAR2 (50) Yes (null)
    GRANT_ID NUMBER (10,0) Yes (null)
    CONTRACT_ID NUMBER (10,0) Yes (null)
    MIN_AMOUNT NUMBER (11,2) Yes 0
    Tuesday, October 30, 2012 4:19 PM
  • User269602965 posted

    I would specify parameter direction for your IN variables as well as OUT

    And, as illogical as it sounds, I have had this suggestion fix problems for me at times as well.

    For the Oracle Column Object with datatype NUMBER(x,0) which is an integer by definition,

    use .Decimal data type in your .NET parameter definition instead of Int32.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 30, 2012 6:10 PM