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.

Answer 2

there seems to be several problems with your code. Oracle Type LONG is not same like in C#, LONG in Oracle DB allows you to store character data up to 2GB size. In C# it's an numeric type using 64 Bit. Since your submitted code does not explain what type of data your parameters in_MemNbr, in_SubNbr and in_StatCds in the package function are, i can only guess, what it is depending on your definitions of the parameter list in your c# method.

Your parameter names in C# in the "new OracleParameter("")" statements does not match the function parameters exactly. In Pl/Sql you added an "in_" prefix, but removed it in the c# code. With "cmd.BindByName = true;" you say to ODP.Net "Hey, bind the parameters in the collection by name rather than using position". In this case they must match exactly.

Your C# return value of the method is int (System.Int32), the return value of the PlSql package function is NUMBER. ODP.Net seems to return decimal in C# in case of numbers without specified scale. Maybe you run into a conversion/invalidcast exception when ODP.Net tries to convert the oracle number type into short (Int16) internally. Maybe you get an out of range exception when the returned count is greater than short.MaxValue. Try to specify Int32 as return value in your return value parameter creation.

OracleCommand implements IDisposable interface. Please ensure that your command is disposed, when not needed anymore, since the implementation of IDisposable interfaces in objects shows you that the object creates/uses some resources (managed or unmanaged) and must release them, when operations finished. Shortest way is to use the "using" keyword of C#, which ensures a call of cmd.Dispose() when code execution leaves the block regardless if exception occured or block ends with success.

    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)
    {
        using (var cmd = new OracleCommand("pack_SomePack.func_SearchRowCount", this.Connection))
        using (var result = new OracleParameter("result", OracleDbType.Int32, ParameterDirection.ReturnValue))
        using (var fromDateParam = new OracleParameter("in_FromDate", OracleDbType.Date, fromDate, ParameterDirection.Input))
        using (var thruDateParam = new OracleParameter("in_ThruDate", OracleDbType.Date, thruDate, ParameterDirection.Input))
        using (var grpCdsParam = new OracleParameter("in_GrpCds", OracleDbType.Varchar2, grpCds, ParameterDirection.Input))
        using (var catCdsParam = new OracleParameter("in_CatCds", OracleDbType.Varchar2, catCds, ParameterDirection.Input))
        using (var typCdsParam = new OracleParameter("in_TypCds", OracleDbType.Varchar2, typCds, ParameterDirection.Input))
        using (var memNbrParam = new OracleParameter("in_MemNbr", OracleDbType.Int64, memNbr, ParameterDirection.Input))
        using (var subNbrParam = new OracleParameter("in_SubNbr", OracleDbType.Int64, SubNbr, ParameterDirection.Input))
        using (var searchByParam = new OracleParameter("in_SearchBy", OracleDbType.Varchar2, searchBy, ParameterDirection.Input))
        using (var dispActivityParam = new OracleParameter("in_dispActivity", OracleDbType.Varchar2, dispActivity, ParameterDirection.Input))
        using (var statCdsParam = new OracleParameter("in_StatCds", OracleDbType.Varchar2, statCds, ParameterDirection.Input))
        using (var uncategorizedYnParam = new OracleParameter("in_UncategorizedYN", OracleDbType.Char, showUncategorized ? "Y" : "N", ParameterDirection.Input))
        using (var debugYnParam = new OracleParameter("in_DebugYN", OracleDbType.Char, debugYN, ParameterDirection.Input))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(result);
            cmd.Parameters.Add(fromDateParam);
            cmd.Parameters.Add(thruDateParam);
            cmd.Parameters.Add(grpCdsParam);
            cmd.Parameters.Add(catCdsParam);
            cmd.Parameters.Add(typCdsParam);
            cmd.Parameters.Add(memNbrParam);
            cmd.Parameters.Add(subNbrParam);
            cmd.Parameters.Add(searchByParam);
            cmd.Parameters.Add(dispActivityParam);
            cmd.Parameters.Add(statCdsParam);
            cmd.Parameters.Add(uncategorizedYnParam);
            cmd.Parameters.Add(debugYnParam);

            cmd.BindByName = true;

            cmd.ExecuteNonQuery();

            return Convert.ToInt32(result.Value);
        }
    }