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

Question

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;

You are binding by name, are you including the bind name? Does it match the parameter name? Is that how you pass in function parameters via C#? Does it expect the string to contain :bind markers in place of the parameters? Have you tried bind by position instead? Does the out bind for the function result know that it is to receive the result and not some OUT parameter? These are all client programming questions having to do with your particular C#-to-database client API, and I can only help with the Oracle part. But these are the questions I would dig into if I were you. — Paul W, Apr 12 '23 at 02:10

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

0 Answers0