No return value when calling Oracle function from C#

Question

I get a return value back from this Oracle function when executing it from Oracle SQL Developer (SELECT OSIEXTN.Validate_ID ('pers',1315) FROM DUAL;):

create or replace FUNCTION Validate_ID(v_Type   IN VARCHAR2,
                                       v_ID     IN NUMBER)
    RETURN VARCHAR2
IS
    v_validation   VARCHAR2 (10);
    v_cnt          NUMBER;
    v_ext          NUMBER;
BEGIN
    IF v_type = 'org'
    THEN
        SELECT COUNT (*) INTO v_cnt
          FROM org
         WHERE orgnbr = v_ID;

        CASE v_cnt
            WHEN 1
            THEN
                v_validation := 'True';
            ELSE
                v_validation := 'False';
        END CASE;
    ELSIF v_type = 'pers'
    THEN
        SELECT COUNT (*) INTO v_cnt
          FROM pers
         WHERE persnbr = v_ID;

        CASE v_cnt
            WHEN 1
            THEN
                v_validation := 'True';
            ELSE
                v_validation := 'False';
        END CASE;
    ELSIF v_type = 'acct'
    THEN
        SELECT COUNT (*) INTO v_cnt
          FROM acct
         WHERE acctnbr = v_ID;
         
        SELECT COUNT(*) INTO v_ext
          FROM EXTENTITY
        WHERE EXTENTITYID = v_ID;

        CASE v_cnt
            WHEN 1
            THEN
                v_validation := 'True';
            ELSE
                CASE v_ext
                WHEN 1
                THEN
                  v_validation := 'True';
                ELSE
                  v_validation := 'False';
                END CASE;
        END CASE;
    END IF;

    RETURN v_validation;
END;

But when calling it from this C# code, the value coming back is simply {}.

using (IDbConnection conn = DatabaseFactory.CreateConnection())
{
    conn.Open();

    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "OSIEXTN.Validate_ID";

        cmd.AddParameter(
            "v_Type",
            ParameterDirection.Input,
            OracleDbType.Varchar2,
            entityType);

        cmd.AddParameter(
            "v_ID",
            ParameterDirection.Input,
            OracleDbType.Long,
            id);

        OracleParameter retVal = new OracleParameter("retVal", OracleDbType.Varchar2);
        retVal.Direction = ParameterDirection.ReturnValue;
        cmd.Parameters.Add(retVal);

        cmd.ExecuteNonQuery();
        object o = retVal.Value;
    }
}

Why am I not seeing a "True" or "False"?

Answer 1

Just a small comment; as Gary commented, the function call name doesn't match.

The issue is occurring because you are trying to call a function like a stored procedure with

cmd.CommandType = CommandType.StoredProcedure;

which doesn't work for functions. Either create a stored procedure that calls the function, or use the following approach(plsql implementation is mocked for demo purposes);

CREATE OR REPLACE FUNCTION SAMPLEFUNCTION (v_Type   IN VARCHAR2,
                                       v_ID     IN NUMBER)
    RETURN VARCHAR2
IS
    
BEGIN
  RETURN 'True';
END SAMPLEFUNCTION;

Then call the function with the following code;

var sqlToExecute = "BEGIN " +
"SELECT SAMPLEFUNCTION(:v_Type, :v_ID) INTO :retVal FROM DUAL; " +
"END;";

            using (var conn = new OracleConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
            {
                conn.Open();

                using (var cmd = new OracleCommand(sqlToExecute, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlToExecute;

                    // Input parameters
                    var oraParamVtype = new OracleParameter("v_Type", OracleDbType.Varchar2, ParameterDirection.Input);
                    oraParamVtype.Value = "A";
                    cmd.Parameters.Add(oraParamVtype);

                    var oraParamVid = new OracleParameter("v_ID", OracleDbType.Long, ParameterDirection.Input);
                    oraParamVid.Value = 1;
                    cmd.Parameters.Add(oraParamVid);

                    // Return value
                    var retVal = new OracleParameter("retVal", OracleDbType.Varchar2, ParameterDirection.Output) { Size = 5 };
                    cmd.Parameters.Add(retVal);

                    cmd.ExecuteNonQuery();
                    Console.WriteLine($"Query result: {retVal.Value}");
                }
            }
        }

Please note that i used CommandType.Text