Call an Oracle Function from C# with Nulls

Question

I'm trying to call an Oracle Function from our C# application, but I either get the following errors. I think I have two problems:

1. I want to call this function, but some of the parameters can be null on the C# side so I don't know how to handle them.

2. I don't know if I need to add the return value to the parameters with ParameterDirection.ReturnValue on the OracleParameter object.

This is what I'm trying:

public int GetActivityRowCount(DateTime fromDate, DateTime thruDate, string grpCds, string catCds, string typCds, long? memNbr, long? subNbr, string searchBy, string dispActivity, string statCds, bool showUncategorized, string debugYN)
{
    OracleCommand cmd = null;

    try
    {
        StringBuilder sql = new StringBuilder();
        sql.Append(" pack_SomePack.func_SearchRowCount");


        cmd = new OracleCommand(sql.ToString(), this.Connection);
        cmd.CommandType = CommandType.StoredProcedure;

        // Don't know if I should add this guy
        // cmd.Parameters.Add(new OracleParameter("RowCount", OracleDbType.Int16, ParameterDirection.ReturnValue));

        cmd.Parameters.Add(new OracleParameter("FromDate", OracleDbType.Date, fromDate, ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("ThruDate", OracleDbType.Date, thruDate, ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("grpCds", OracleDbType.Varchar2, grpCds, ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("catCds", OracleDbType.Varchar2, catCds, ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("typCds", OracleDbType.Varchar2, typCds, ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("memNbr", OracleDbType.Long, memNbr, ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("SubNbr", OracleDbType.Long, SubNbr, ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("searchBy", OracleDbType.Varchar2, searchBy, ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("dispActivity", OracleDbType.Varchar2, dispActivity, ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("statCds", OracleDbType.Varchar2, statCds, ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("showUncategorized", OracleDbType.Char, showUncategorized? "Y" : "N", ParameterDirection.Input));
        cmd.Parameters.Add(new OracleParameter("debugYN", OracleDbType.Varchar2, debugYN, ParameterDirection.Input));

        cmd.BindByName = true;

        int activityRowCount = Convert.ToInt16(cmd.ExecuteScalar()); // Error here

        return activityRowCount;
    }

My function does the following:

FUNCTION func_SearchRowCount
(
    in_FromDate         IN DATE,
    in_ThruDate         IN DATE,
    in_GrpCds           IN VARCHAR2,
    in_CatCds           IN VARCHAR2,
    in_TypCds           IN VARCHAR2,
    in_MemNbr           IN Actv.PersNbr%TYPE,
    in_SubNbr           IN Actv.SubNbr%TYPE,
    in_SearchBy         IN VARCHAR2,
    in_dispActivity     IN VARCHAR2,
    in_StatCds          IN Ams.StatCd%TYPE,
    in_UncategorizedYN  IN CHAR,
    in_DebugYN          IN CHAR
) RETURN NUMBER AS

    lvnCount            NUMBER;
    lvsSqlStr           VARCHAR2(2000);

BEGIN
    lvsSqlStr := 'SELECT COUNT(*) FROM SomeTable WHERE (Include a bunch of clauses..)';
    BEGIN
        EXECUTE IMMEDIATE lvsSqlStr
                INTO lvnCount
                USING (All the parameters);
    END;

    RETURN lvnCount;

END func_SearchRowCount;

I get the following error when running what's above.

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

All the variables are bound with the correct amount, although I read somewhere that ODP.NET will remove the parameters with null as there .Value. Is this true? What should I pass in to indicate that there is no value for that parameter then?

Answer 1

You need 4 things at a minimum:

• The first two: call ExecuteNonQuery and not ExecuteScalar as discussed in this MSDN thread and create the return value parameter. About a third of the way down on Accessing Oracle 9i Stored Procedures it shows this code and says

You execute the function in the same way as a stored procedure. Use a ParameterDirection.ReturnValue parameter to get the result returned by the function.

• On the third, do use DbNull.Value because it is specifically designed as a placeholder to represent null values in a database, whereas null has meaning only for .NET. (Well, null is probably ok because the Oracle driver is probably smart enough to handle it; DbNull.Value is a good habit though because you're being explicit). You can do something like

new OracleParameter("typCds", OracleDbType.Varchar2, typCds ?? (object)DbNull.Value, ParameterDirection.Input));

• And finally, you have bind by name on your parameters, but the names don't match the names of your parameters. Match the names exactly or bind by position.

As to the specific error, the return value is "an argument" and didn't bind he parameters correctly. Oracle wanted 13 parameters and you effectively gave it none.