none
System.Data.SQLClient throwing wrong number or types of arguments error RRS feed

  • Question

  • Hi All,

    We are getting error: 
    {"ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'PR_SYNC_ADDMERGEHIST'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    "}       

    when using    System.Data.OracleClient. The parameters passed to the SP is all correct. There are no null values passed to the parameters. all the parameters passed has valid values.
    We are not getting this error if we use Oracle.DataAccess component to connect to Oracle.

    Please let us know, what could be the cause of this problem.

    Thanks in advance

    Thursday, April 22, 2010 1:29 PM

Answers

  • I don't use the "@" symbol in my Oracle parameter names (in .NET code). Don't the names have to be the same as those in your stored procedure?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 23, 2010 12:49 PM
  • Hello,

    I did not test your code snippet on my local machine, however, I think I agree with Paul. To call a stored procedure using System.Data.OracleClient namespace, you don't have to use '@' symbol. Here is a sample/tutorial of doing that, I believe it could help you.

    As you could see, to call a stored procedure with parameters, you could use a code snippet as below:

    OracleCommand myCmd = new OracleCommand();
    myCmd.Connection = myConn;
    myCmd.CommandText = "my_procedure_name";
    myCmd.CommandType = CommandType.StoredProcedure;
    myCmd.Parameters.Add("parameter1", OracleType.Number).Value = 10;
    myCmd.Parameters.Add("parameter2", OracleType.Number).Direction = ParameterDirection.Output;
    

    Please have a try and let me know if this helps.
    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Wednesday, April 28, 2010 5:02 AM

All replies

  • Hi Shaila,

    I think it would be helpful if you posted a small code sample for this problem.

    Thanks

    Ralf

    Thursday, April 22, 2010 2:21 PM
  • Yes, we would need to see the stored procedure and code (w/parameters) that is calling the sp in order to troubleshoot.

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, April 22, 2010 6:11 PM
  • Following is the code of sored procedure:

    PROCEDURE pr_AddMergeHistory
    (
       in_Dealer_ID IN DATASYNC_MERGE_HISTORY.DEALER_ID%TYPE
           ,in_From_Sequence_No IN DATASYNC_MERGE_HISTORY.FROM_SEQ_NO%TYPE
           ,in_To_Sequence_No IN DATASYNC_MERGE_HISTORY.TO_SEQ_NO%TYPE
        ,in_Merge_Date IN DATASYNC_MERGE_HISTORY.MERGE_DATE%TYPE
           ,in_Merge_File_Name IN DATASYNC_MERGE_HISTORY.MERGE_FILE_NAME%TYPE
               ,in_Status IN DATASYNC_MERGE_HISTORY.STATUS%TYPE
           ,in_MESSAGE IN DATASYNC_MERGE_HISTORY.MESSAGE%TYPE
        ,in_NonDLr_From_Sequence_No IN DATASYNC_MERGE_HISTORY.NONDLR_FROM_SEQ_NO%TYPE
        ,in_NonDLr_To_Sequence_No IN DATASYNC_MERGE_HISTORY.NONDLR_TO_SEQ_NO%TYPE

    )
    IS
    BEGIN

     INSERT INTO DATASYNC_MERGE_HISTORY
    (
          History_ID
         ,Dealer_ID
                  ,From_Seq_No
                  ,To_Seq_No
                  ,Merge_Date
                  ,Merge_File_Name
         ,Status
                  ,Message
               ,NonDlr_From_Seq_No
               ,NonDlr_To_Seq_No
    )
     VALUES
    (
        seq_merge_history_id.nextval
     ,in_Dealer_ID
        ,in_From_Sequence_No
       ,in_To_Sequence_No
       ,SYSDATE
       ,in_Merge_File_Name
       ,in_Status
       ,in_Message
       ,in_NonDlr_From_Sequence_No
       ,in_NonDlr_To_Sequence_No
    );

    pr_move_enq_aware_temp(in_Dealer_ID);
    pr_move_pref_charact_temp(in_Dealer_ID);
    END;

    Following is the vb.net code which is used to call the stored procedure:


         oParamDo = New ParamDO()
                oParamDo.Name = "@in_Dealer_ID"
                'oParamDo.Value = m_iDealerId
                oParamDo.Value = 10280
                oParamDo.Size = 8
                oParamDo.Type = DbType.Int32
                oParamDo.Direction = ParameterDirection.Input
                oParamDOList.Add(oParamDo)

                oParamDo = New ParamDO()
                oParamDo.Name = "@in_From_Sequence_No"
                If bNoHeader Then
                    oParamDo.Value = m_dtHeader.Rows(0).Item(FROM_SEQUENCE_NO)
                Else
                    oParamDo.Value = -1
                End If
                oParamDo.Value = 1
                oParamDo.Size = 10
                oParamDo.Type = DbType.Int32
                oParamDo.Direction = ParameterDirection.Input
                oParamDOList.Add(oParamDo)

                oParamDo = New ParamDO()
                oParamDo.Name = "@in_To_Sequence_No"
                If bNoHeader Then
                    oParamDo.Value = m_dtHeader.Rows(0).Item(TO_SEQUENCE_NO)
                Else
                    oParamDo.Value = -1
                End If
                oParamDo.Value = 2
                oParamDo.Size = 10
                oParamDo.Type = DbType.Int32
                oParamDo.Direction = ParameterDirection.Input
                oParamDOList.Add(oParamDo)

                oParamDo = New ParamDO()
                oParamDo.Name = "@in_Merge_Date"
                oParamDo.Value = Date.Now
                oParamDo.Size = 50
                oParamDo.Type = DbType.Date
                oParamDo.Direction = ParameterDirection.Input
                oParamDOList.Add(oParamDo)

                oParamDo = New ParamDO()
                oParamDo.Name = "@in_Merge_File_Name"
                If bNoHeader Then
                    oParamDo.Value = m_dtHeader.Rows(0).Item(FILE_NAME)
                Else
                    oParamDo.Value = "No File"
                End If
                oParamDo.Value = "No File"
                oParamDo.Size = 60
                oParamDo.Type = DbType.String
                oParamDo.Direction = ParameterDirection.Input
                oParamDOList.Add(oParamDo)

                oParamDo = New ParamDO()
                oParamDo.Name = "@in_Status"
                'oParamDo.Value = m_iStatus
                oParamDo.Value = 1
                oParamDo.Size = 1
                oParamDo.Type = DbType.Int16
                oParamDo.Direction = ParameterDirection.Input
                oParamDOList.Add(oParamDo)

                oParamDo = New ParamDO()
                oParamDo.Name = "@in_MESSAGE"
                'oParamDo.Value = m_strMessage
                oParamDo.Value = "TEST"
                oParamDo.Size = 250
                oParamDo.Type = DbType.String
                oParamDo.Direction = ParameterDirection.Input
                oParamDOList.Add(oParamDo)

                oParamDo = New ParamDO()
                oParamDo.Name = "@in_NonDLr_From_Sequence_No"
                If bNoHeader Then
                    oParamDo.Value = m_dtHeader.Rows(0).Item(NONDLR_FROM_SEQUENCE_NO)
                Else
                    oParamDo.Value = -1
                End If
                oParamDo.Value = 1
                oParamDo.Size = 10
                oParamDo.Type = DbType.Int32
                oParamDo.Direction = ParameterDirection.Input
                oParamDOList.Add(oParamDo)

                oParamDo = New ParamDO()
                oParamDo.Name = "@in_NonDLr_To_Sequence_No"
                If bNoHeader Then
                    oParamDo.Value = m_dtHeader.Rows(0).Item(NONDLR_TO_SEQUENCE_NO)
                Else
                    oParamDo.Value = -1
                End If
                oParamDo.Value = 1
                oParamDo.Size = 10
                oParamDo.Type = DbType.Int32
                oParamDo.Direction = ParameterDirection.Input
                oParamDOList.Add(oParamDo)
                'cmd.Parameters.Add(New OracleParameter("arg1", OracleType.Number, 16)).Value = 10

                oDataMgr = New DataManager()
                'Calls the stored procedure which updates Merge History Table
                oDataMgr.ExecuteNonQueryProc(foConn, "pr_Sync_AddMergeHist", oParamDOList)

    The values passed to the parameter are all hardcoded just to check, if the data is getting saved by hardcoding data.

    Friday, April 23, 2010 3:37 AM
  • I don't use the "@" symbol in my Oracle parameter names (in .NET code). Don't the names have to be the same as those in your stored procedure?

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 23, 2010 12:49 PM
  • Hello,

    I did not test your code snippet on my local machine, however, I think I agree with Paul. To call a stored procedure using System.Data.OracleClient namespace, you don't have to use '@' symbol. Here is a sample/tutorial of doing that, I believe it could help you.

    As you could see, to call a stored procedure with parameters, you could use a code snippet as below:

    OracleCommand myCmd = new OracleCommand();
    myCmd.Connection = myConn;
    myCmd.CommandText = "my_procedure_name";
    myCmd.CommandType = CommandType.StoredProcedure;
    myCmd.Parameters.Add("parameter1", OracleType.Number).Value = 10;
    myCmd.Parameters.Add("parameter2", OracleType.Number).Direction = ParameterDirection.Output;
    

    Please have a try and let me know if this helps.
    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Wednesday, April 28, 2010 5:02 AM