none
PLS-00306: wrong number or types of arguments in call to 'STORED_PROCEDURE'

    Question

  • Hello,

    I use c# and try to execute a StoredProcedure on a Oracle Database. I use  the System.Data.OracleClient.

    This is how the StoredProcedure is executed via SQL:

    DECLARE 
      P_SUCHART VARCHAR2(32767);
      P_SUCHWERT VARCHAR2(32767);
      P_DATUM_VON DATE;
      P_DATUM_BIS DATE;
      P_REF SYS_REFCURSOR;
    
    BEGIN 
      P_SUCHART := 'I';
      P_SUCHWERT := 'F503351515/2/1                  ';
      P_DATUM_VON := NULL;
      P_DATUM_BIS := NULL;
      P_REF := NULL;
    
      INSTANCE.STOREDPROCEDURE ( P_SUCHART, P_SUCHWERT, P_DATUM_VON, P_DATUM_BIS, P_REF );
       :r := P_REF;
    END;

    This is the C# code were I try to do the same. It crashed at "OracleDataReader objReader = objCmd.ExecuteReader();"

    private string[] callStoredProcedure()
            {
                string[] featureIDs = null;
    
                            using (OracleConnection objConn = new OracleConnection(connectionString))
                {
                    OracleCommand objCmd = new OracleCommand();
                    objCmd.Connection = objConn;
                    objCmd.CommandText = INSTANCE.STORED_PROCEDURE_NAME;
                    objCmd.CommandType = CommandType.StoredProcedure;
    
                    objCmd.Parameters.Add(P_SUCHART, OracleType.VarChar, 32767).Value = suchArt;
                    objCmd.Parameters.Add(P_SUCHWERT, OracleType.VarChar, 32767).Value = suchWert;
                    objCmd.Parameters.Add(P_DATUM_VON, OracleType.DateTime).Value = null;
                    objCmd.Parameters.Add(P_DATUM_BIS, OracleType.DateTime).Value = null;
                    objCmd.Parameters.Add(P_REF , OracleType.Cursor).Direction = ParameterDirection.Output;
    
                    try
                    {
                        objConn.Open();
                        OracleDataReader objReader = objCmd.ExecuteReader();
    
                        featureIDs = new string[objReader.FieldCount];
                        int i = 0;
                        while (objReader.Read())
                        {
                            featureIDs[i++] = objReader[7].ToString();
                        }
                    }
                    catch (Exception)
                    {
                        throw new Exception("Failure");
                    }
                    return featureIDs;
                }
            }
    Maybe theres something wrong with the datatypes.

    the SQL above uses VARCHAR2, DATE and SYS_REFCURSOR.
    The Oracle Client only offers Varchar, DateTime and Cursor.

    But I do not know how to do in another way.

    The count of parameters is also correct I would say. Only my cursor is declared as Outputparameter, is this not correct?
    The stored procedure is expecting a ref parameter as cursor and this one shall be used to read the data returned.

    Thanks a lot for any help.


    • Edited by Kaspatoo Tuesday, February 21, 2012 9:40 PM
    Tuesday, February 21, 2012 2:43 PM

Answers

  • Solution found!

    http://stackoverflow.com/questions/950404/oracle-net-error-wrong-number-or-type-of-arguments

    the Problem is here:

    objCmd.Parameters.Add(P_DATUM_VON, OracleType.DateTime).Value = null; objCmd.Parameters.Add(P_DATUM_BIS, OracleType.DateTime).Value = null;

    must be:

                    objCmd.Parameters.Add(P_DATUM_VON, OracleType.DateTime).Value = DBNull.Value;
                    objCmd.Parameters.Add(P_DATUM_BIS, OracleType.DateTime).Value = DBNull.Value;
    • Marked as answer by Kaspatoo Wednesday, February 22, 2012 1:50 PM
    Wednesday, February 22, 2012 1:50 PM

All replies

  • You might want to take a look at the below article for syntax. Looking at your Oracle SP, I don't see parameters defined as IN or OUT. I also do not see the procedure name.

    http://msdn.microsoft.com/en-us/library/ms971506.aspx

    In the .NET code, the parameter names should be enclosed within double quotes.

    objCmd.Parameters.Add("P_DATUM_VON", OracleType.DateTime).Value = null;


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, February 21, 2012 4:10 PM
  • hi thanks for the  answer

    about the double quotes:
    i store the name in string variables thats why double quotes are missing.

    U said "Looking at your Oracle SP, I don't see parameters defined as IN or OUT."What do you mean with this?
    My last parameter has

    .Direction = ParameterDirection.Output;

    so its an output param, the others are default input i think.

    My procedurename appears in line 9 (there the same with double quotes):

    objCmd.CommandText = INSTANCE.STORED_PROCEDURE_NAME;

    Tuesday, February 21, 2012 4:44 PM
  • I was referring to the Oracle SP, not the .NET code. Something like the below example:

     PROCEDURE STORED_PROCEDURE_NAME(P_SUCHART    IN VARCHAR2,
                                     P_SUCHWERT   IN VARCHAR2,
                                     P_DATUM_VON  IN DATE,
                                     P_DATUM_BIS  IN DATE,
                                     P_REF        OUT T_CURSOR) IS
    
     BEGIN
     '...
     END;


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, February 21, 2012 5:24 PM
  • the stored procedure still exists

    i only know that I have to use the code above.

     INSTANCE.STOREDPROCEDURE ( P_SUCHART, P_SUCHWERT, P_DATUM_VON, P_DATUM_BIS, P_REF );

    these are the parameters the procedure will take. How they ar declared can be seen in first post.
    The P_REF shall give me a cursor back with which I shall read all lines of the resultset.

    But I guess with the IN and OUT it is as you posted and as I expcected before and as the code implies.

    Tuesday, February 21, 2012 9:45 PM
  • Solution found!

    http://stackoverflow.com/questions/950404/oracle-net-error-wrong-number-or-type-of-arguments

    the Problem is here:

    objCmd.Parameters.Add(P_DATUM_VON, OracleType.DateTime).Value = null; objCmd.Parameters.Add(P_DATUM_BIS, OracleType.DateTime).Value = null;

    must be:

                    objCmd.Parameters.Add(P_DATUM_VON, OracleType.DateTime).Value = DBNull.Value;
                    objCmd.Parameters.Add(P_DATUM_BIS, OracleType.DateTime).Value = DBNull.Value;
    • Marked as answer by Kaspatoo Wednesday, February 22, 2012 1:50 PM
    Wednesday, February 22, 2012 1:50 PM