Execute a pl/sql function with OracleCommand

Question

i have this pl/sql function, the only thing it does is validate that the user exist in the database, if the user exists this returns "Y" but if the user dont exist this return "N", what I want is get the value that I return in pl/sql in c #.

I am using oracle 10g

            CREATE OR REPLACE FUNCTION KRIST.f_Login (userName IN VARCHAR2,
                                                        password IN VARCHAR2)
            RETURN VARCHAR2 
            IS
                CURSOR USERFINDER IS
                    SELECT IdEmpleado
                    FROM EMPLEADO
                    WHERE Usuario=userName
                    AND Clave=password;
                id number;
                returnVal VARCHAR2(1);
            BEGIN
                OPEN USERFINDER;
                FETCH USERFINDER INTO id; 
                IF(id IS NULL) THEN
                    returnVal:='Y';
                    RETURN returnVal;
                END IF;
                returnVal:='N';
                RETURN returnVal;
                CLOSE USERFINDER;
            END;
            /

how I can perform this function and get the result in a variable... i have thos code but dont works

                OracleCommand cmd = new OracleCommand("krist.p_login",conn);
                cmd.CommandType = CommandType.StoredProcedure;  // use StoredProcedure with Functions as well
                OracleParameter returnVal = new OracleParameter("returnVal",null);
                OracleParameter p_one = new OracleParameter("userName","kristian");
                OracleParameter p_two = new OracleParameter("password", "kristian");
                returnVal.OracleType = OracleType.VarChar;
                returnVal.Size = 1;
                p_one.OracleType = OracleType.VarChar;
                p_two.OracleType = OracleType.VarChar;
                p_one.DbType = DbType.String;
                p_two.DbType = DbType.String;
                returnVal.DbType = DbType.String;
                returnVal.Direction = ParameterDirection.ReturnValue;
                p_one.Direction = ParameterDirection.Input;
                p_two.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(p_one);
                cmd.Parameters.Add(p_two);
                cmd.Parameters.Add(returnVal);
                cmd.ExecuteNonQuery();
                String bval = Convert.ToString(returnVal.Value);
                return bval;

Answer 1

The following code works for me.
NB: Your pl/sql code called the function KRIST.f_Login, but your c# called it krist.p_login
NB2: Your pl/sql code used Varchar2, but your c# used varchar
NB3: I am using Oracle.DataAccess.dll
NB4: I assume your return value buffer size could be 1, but try different sizes.

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

int RETURN_VALUE_BUFFER_SIZE = 32767; 
OracleCommand cmd = new OracleCommand();
try {
    cmd.Connection = conn;
    cmd.CommandText = "KRIST.f_Login";
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("returnVal", OracleDbType.Varchar2, RETURN_VALUE_BUFFER_SIZE);  
    cmd.Parameters["returnVal"].Direction = ParameterDirection.ReturnValue;

    cmd.Parameters.Add("userName", OracleDbType.Varchar2);
    cmd.Parameters["userName"].Value = "kristian";

    cmd.Parameters.Add("password", OracleDbType.Varchar2);
    cmd.Parameters["password"].Value = "kristian";

    cmd.ExecuteNonQuery();
    string bval = cmd.Parameters["returnVal"].Value.ToString();
    return bval;
} catch (Exception e) {
    // deal with exception 
} finally {
    command.Dispose();
    connection.Close();
    connection.Dispose();
}

Answer 2

As far as I remember If you are using ODP.NET you need to provide retVal parameter as first.

Something is wrong with ODP.NET and it dosn't bind parameters with provided parameter names but with order of parameters.

So simply change order to:

cmd.Parameters.Add(returnVal);
cmd.Parameters.Add(p_one);
cmd.Parameters.Add(p_two);

And in my sources I found that return parameter i called "RETURN" (not sure if it counts):

OracleParameter returnVal = new OracleParameter("RETURN",null);

A ha and one more thing. It will never reach last line - cuase return would terminate execute. Close it as soon as you don't need it anymore.

RETURN returnVal;
CLOSE USERFINDER; --<<-- won't close this cursor