Wrong number or types of arguments in call to 'Function'

128 views Asked by At

I have a function that is in a package in Oracle. It takes the job name and after associating with the table, it returns a number of 0 or 1.

In my code this is how I pass the job name to the function:

checkJob = tool.ExecuteStoredFunction(connString2, "SCHEMANAME.PACKAGE_NAME.FUNCTION_NAME", CommandType.StoredProcedure, oraPrm, true);

And this is my ExecuteStoredFunction method:

public string ExecuteStoredFunction(string conn, string sql, CommandType cType, OracleParameter[] oraPrm, bool hasReturnValue)
{
        OracleParameter p_ReturnValue;

        using (OracleConnection connection = new OracleConnection(conn))
        {
            // Create the Command and Parameter objects.
            OracleCommand command = new OracleCommand(sql, connection);

            try
            {
                connection.Open();
                command.CommandText = sql;
                command.CommandType = cType;
                command.BindByName = true;

                if (oraPrm.Count() > 0)
                {
                    for (int i = 0; i < oraPrm.Count(); i++)
                    {
                        oraPrm[i].Direction = ParameterDirection.Input; // Set parameter direction to Input
                        command.Parameters.Add(oraPrm[i]);
                        //command.Parameters.Add(oraPrm[i]);
                    }
                }

                p_ReturnValue = new OracleParameter("p_retval", OracleDbType.Int16);
                p_ReturnValue.Direction = ParameterDirection.ReturnValue;

                if (hasReturnValue)
                {
                    command.Parameters.Add(p_ReturnValue);
                }

                foreach (OracleParameter param in command.Parameters)
                {
                    System.Diagnostics.Debug.WriteLine("Parameter Name: " + param.ParameterName);
                    System.Diagnostics.Debug.WriteLine("Parameter Value: " + param.Value);
                }

                System.Diagnostics.Debug.WriteLine(command.CommandText);
                // Use ExecuteScalar() to execute the stored function and retrieve the return value
                object result = command.ExecuteScalar();

                if (result != null && hasReturnValue)
                {
                    System.Diagnostics.Debug.WriteLine("Return value: " + result.ToString());
                }

                command.Parameters.Clear();
                connection.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                command.Parameters.Clear();
                connection.Close();
                return "fail";
            }
        }

        if (hasReturnValue)
        {
            return p_ReturnValue.Value.ToString();
        }
        else
        {
            return "ok";
        }
    }

What I don't understand is that all the values are being passed, yet I am getting this error:

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

Why is this happening?

When I run the SQL in TOAD like this, it works:

SELECT
    SCHEMANAME.PACKAGE_NAME.FUNCTION_NAME('JOB_NAME')
FROM DUAL;
0

There are 0 answers