How to resolve this "PLS-00306: wrong number or types of arguments in call..."

Question

I've been struggling with a call to an Oracle database for over a day now. I have no access to the database other than programmatically, although a DBA was able to give me the following:

desc appowner.member_data_package

PROCEDURE UPDATE_NOTES_V001
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
TAU_UID_IN                     VARCHAR2                IN
INCOMING_FUNC_IN               VARCHAR2                IN
INCOMING_TEXT_IN               VARCHAR2                IN
SYS_SOURCE_IN                  VARCHAR2                IN
USER_ID_IN                     VARCHAR2                IN
O_STATUS                       VARCHAR2                OUT

My code looks like this:

using (var connection = new OracleConnection(connectionString))
{
    connection.Open();

    OracleCommand command = new OracleCommand
    {
        Connection = connection,
        CommandType = CommandType.StoredProcedure,
        CommandText = "appowner.MEMBER_DATA_PACKAGE.UPDATE_NOTES_V001",
        BindByName = true
    };

    command.Parameters.Add(new OracleParameter("O_STATUS", OracleDbType.Varchar2, ParameterDirection.ReturnValue));
    command.Parameters.Add(new OracleParameter("TAU_UID_IN", OracleDbType.Varchar2, ParameterDirection.Input)).Value = "7400";
    command.Parameters.Add(new OracleParameter("INCOMING_FUNC_IN", OracleDbType.Varchar2, ParameterDirection.Input)).Value = "PROVCOUNT";
    command.Parameters.Add(new OracleParameter("INCOMING_TEXT_IN", OracleDbType.Varchar2, ParameterDirection.Input)).Value = "Pypestream testing.";
    command.Parameters.Add(new OracleParameter("SYS_SOURCE_IN", OracleDbType.Varchar2, ParameterDirection.Input)).Value = "CRM";
    command.Parameters.Add(new OracleParameter("USER_ID_IN", OracleDbType.Varchar2, ParameterDirection.Input)).Value = "jac";

    command.ExecuteNonQuery();
    Console.WriteLine($"Insert output value is '{command.Parameters["O_STATUS"].Value.ToString()}'");

}

The error I get when I run this is:

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

As far as I can tell I do have the right number and types of arguments, but if somebody wiser than me in the ways of Oracle (which, basically, is anyone) can point out where I messed up that would be awesome.

If that part looks right and the error is a red herring of some sort, what else can I look at? Is there some other way to call this procedure? Keep in mind I have no tools other than Visual Studio 2017. I cannot download any helpful extensions. I have no direct access to the database other than an email to the DBA.

I also confess that I could be on a completely wrong tack here. I'm totally guessing the code to insert values into the Oracle database would look like this based on some examples I've seen here and around the 'net. Any advice to tackle this would be helpful.

Answer 1

The call to the Oracle procedures needs to match the type and order of the parameters of the called procedure. Also, a parameter that returns a value is an OutPut parameter. Your code does not match the parameters of the called procedure, hence the error. Change it to:

using (var connection = new OracleConnection(connectionString))
{
connection.Open();

OracleCommand command = new OracleCommand
{
    Connection = connection,
    CommandType = CommandType.StoredProcedure,
    CommandText = "appowner.MEMBER_DATA_PACKAGE.UPDATE_NOTES_V001",
    BindByName = true
};

command.Parameters.Add(new OracleParameter("TAU_UID_IN", OracleDbType.Varchar2, ParameterDirection.Input)).Value = "7400";
command.Parameters.Add(new OracleParameter("INCOMING_FUNC_IN", OracleDbType.Varchar2, ParameterDirection.Input)).Value = "PROVCOUNT";
command.Parameters.Add(new OracleParameter("INCOMING_TEXT_IN", OracleDbType.Varchar2, ParameterDirection.Input)).Value = "Pypestream testing.";
command.Parameters.Add(new OracleParameter("SYS_SOURCE_IN", OracleDbType.Varchar2, ParameterDirection.Input)).Value = "CRM";
command.Parameters.Add(new OracleParameter("USER_ID_IN", OracleDbType.Varchar2, ParameterDirection.Input)).Value = "jac";
command.Parameters.Add(new OracleParameter("O_STATUS", OracleDbType.Varchar2, ParameterDirection.OutPut));

command.ExecuteNonQuery();
Console.WriteLine($"Insert output value is '{command.Parameters["O_STATUS"].Value.ToString()}'");

}