Error on calling stored procedure on Oracle 10g server

Question

I call my stored procedure from C# and I get the following exception:

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

As my procedure has 57 arguments it is not easy to know where is really the problem. Is there a way to have a more detailed message ?

Note : However arguments type and number seem to match !

Here are my stored procedure arguments:

Var_PE_...  IN  NUMBER(8,0),
Var_PE_...  IN  NUMBER(4,0),
Var_PE_...  IN  NUMBER(3,0),
Var_PE_...  IN  DATE,
Var_PE_...  IN  DATE,
Var_PE_...  IN  NUMBER(2,0),
Var_PE_...  IN  NUMBER(4,0),
Var_PE_...  IN  NUMBER(4,0),
Var_PE_...  IN  NUMBER(8,0),
Var_PE_...  IN  NUMBER(7,0),
Var_PE_...  IN  VARCHAR2(1 BYTE),
Var_PE_...  IN  CHAR(1 BYTE),
Var_PE_...  IN  NUMBER(15,0),
Var_PE_...  IN  VARCHAR2(25 BYTE),
Var_PE_...  IN  VARCHAR2(25 BYTE)
Var_PE_...  IN  VARCHAR2(25 BYTE),
Var_PE_...  IN  VARCHAR2(25 BYTE),
Var_PE_...  IN  NUMBER(5,0),
Var_PE_...  IN  VARCHAR2(25 BYTE),
Var_PE_...  IN  VARCHAR2(25 BYTE),
Var_PE_...  IN  NUMBER(5,0),
Var_PE_...  IN  NUMBER(5,0),
Var_PE_...  IN  VARCHAR2(11 BYTE),
Var_PE_...  IN  NUMBER(2,0),
Var_PE_...  IN  CHAR(2 BYTE),
Var_PE_...  IN  VARCHAR2(17 BYTE),
Var_PE_...  IN  DATE,
Var_PE_...  IN  DATE,
Var_PE_...  IN  DATE,
Var_PE_...  IN  NUMBER(12,5),
Var_PE_...  IN  NUMBER(9,2),
Var_PE_...  IN  NUMBER(3,0),
Var_PE_...  IN  NUMBER(9,2),
Var_PE_...  IN  CHAR(2 BYTE),
Var_PE_...  IN  CHAR(3 BYTE),
Var_PE_...  IN  NUMBER(3,0),
Var_PE_...  IN  NUMBER(7,2),
Var_PE_...  IN  NUMBER(3,0),
Var_PE_...  IN  NUMBER(9,2), 
Var_PE_...  IN  NUMBER(9,2),
Var_PE_...  IN  NUMBER(10,0),
Var_PE_...  IN  VARCHAR2(49 BYTE),
Var_PE_...  IN  CHAR(1 BYTE),
Var_PE_...  IN  CHAR(1 BYTE),
Var_PE_...  IN  CHAR(6 BYTE),
Var_PE_...  IN  CHAR(1 BYTE),
Var_PE_...  IN  NUMBER(9,2),
Var_PE_...  IN  VARCHAR2(13 BYTE),
Var_PE_...  IN  NUMBER(2,0),
Var_PE_...  IN  VARCHAR2(2 BYTE),
Var_PE_...  IN  DATE,
Var_PE_...  IN  DATE,
Var_PE_...  IN  VARCHAR2(1 BYTE),
Var_PE_...  IN  NUMBER(2,0),
Var_PE_...  IN  VARCHAR2(20 BYTE),

Var_PS...   OUT VARCHAR2,
Var_PS...   OUT VARCHAR2

And here is my call to this procedure from c#:

l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Date,       0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Date,       0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Date,       0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Date,       0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Date,       0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Double,     0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Double,     0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Double,     0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Double,     0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Double,     0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Double,     0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Double,     0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Double,     0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Date,       0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Date,       0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.Int32,      0, "PE_..."));
l_Params.Add(CreateInputDbParameter(DbType.AnsiString, 0, "PE_..."));

l_Params.Add(CreateOutputDbParameter(DbType.AnsiString, 2000, "PS_..."));
l_Params.Add(CreateOutputDbParameter(DbType.AnsiString, 2000, "PS_..."));

ExecuteStoreProcedure("MY_PROC", l_Params);

Home code for creating parameters:

    public DbParameter CreateInputDbParameter(DbType typeParam, int sizeparam,
        string nameParam, object valueParam)
    {
        DbParameter DbParam = Parameter;
        DbParam.DbType = typeParam;
        DbParam.Size = sizeparam;
        DbParam.Direction = ParameterDirection.Input;
        DbParam.ParameterName = nameParam;
        if (valueParam == null)
        {
            DbParam.Value = DBNull.Value;
        }
        else
        {
            DbParam.Value = valueParam;
        }
        return DbParam;
    }

    public DbParameter CreateOutputDbParameter(DbType typeParam, int sizeparam, string nameParam)
    {
        DbParameter DbParam = Parameter;
        DbParam.DbType = typeParam;
        DbParam.Size = sizeparam;
        DbParam.Direction = ParameterDirection.Output;
        DbParam.ParameterName = nameParam;
        return DbParam;
    }

can you show the code for CreateOutputDbParameter and CreateInputDbParameter? — Damith
you are not create new DbParameter DbParam = new DbParameter(); but assign Parameter, any reason ? — Damith
It looks like the version of CreateInputDbParameter you posted takes 4 parameters, but all the calls shown only supply 3. Is there another version of CreateInputDbParameter which only accepts 3 parameters? Also, it looks like the nameParam argument values passed to CreateInputDbParam and CreateOutputDbParam match the actual names of the parameters as defined in the procedure, and should thus be 'Var_PE...', etc. — Bob Jarvis - Reinstate Monica
@BobJarvis I cut lines, to have a readable code but in real code, I provide full arguments. Parameters names are not important, only their order is used. — sinsedrix
@Damith Parameter is a getter on Command.CreateParameter() — sinsedrix

Damith Damith · Accepted Answer · 2012-06-22T09:09:28

You need to set parameter direction as ParameterDirection.Output if it is output

OracleParameter test= 
    new OracleParameter("testparameter", OracleDbType.Varchar2);
test.Direction = ParameterDirection.Output;
cmd.Parameters.Add(test);

if it is input set it as ParameterDirection.Input

Check size of the parameters, you are not giving size when declare it. And also the type of parameter also important. for example you have NUMBER(15,0) mapped with DbType.Int32, NUMBER(3,0) mapped with DbType.Double

EDIT

You are trying to connect oracle using ADO.NET base classes, you know that even System.Data.OracleClient also deprecated.

The types in System.Data.OracleClient are deprecated. The types are supported in version 4 of the .NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider.

You better to use Oracle Data Provider for .NET and most of these errors will be solved.

Error on calling stored procedure on Oracle 10g server

1 Answers

EDIT