Execute pl / sql function with OracleCommand

Question

I have this pl / sql function, the only thing it does is to check that the user exists in the database, if the user exists, it returns “Y”, but if the user does not exist, it returns “N”, which I want gets 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 can I execute this function and get the result in a variable ... I have this code but it doesn't work

  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 KRIST.f_Login function, 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 that the size of the return value buffer may 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

ODP.net is contacted by default order. This behavior can be changed with: cmd.BindByName = true

Answer 3

As far as I remember If you use ODP.NET, you need to specify the retVal parameter as the first.

Something is wrong with ODP.NET, and it does not associate parameters with the provided parameter names, but with the order of the parameters.

So just change the order to:

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

And in my sources, I found that the returned parameter is called "RETURN" (not sure what it considers):

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

Yeah, and one more thing. It will never reach the last line - cuase return will stop executing. Close it as soon as you no longer need it.

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