Problem getting return value from a function

[Sergio Espallargas]

Hi all,

I have a function that returns wheter a user exists or not in the
database the function looks like this:

function f_user_exists (p_username IN varchar) return number IS
l_return number;
begin
l_return := 0;
begin
SELECT Count(*)
INTO l_return
FROM SECURITY_MASTER s
WHERE s."USER" = p_username and rownum <= 1;
Exception
When no_data_found Then
raise_application_error(-20010, 'User Not Found: '||p_username);
End;
Return l_return;
END f_user_exists;

Here is the code that calls the function, this function works well
using System.Data.OracleClient (just have to change OracleDBType for
OracleType and types are a little bit diferrent as well). My problem
is that I wanted to give it a try to Oracle.Database.Client and
Oracle.Database.Types and the function always returns 0 value using
these directives, anyone knows why is this happening, maybe I'm doing
something wrong in the code.

public int UserExists(string username)
{
OracleConnection conn = UtilityClass.NewConnection();
conn.Open();
OracleCommand cmd = new
OracleCommand("pkg_authentication.f_user_exists", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_username", OracleDbType.Varchar2,
username.ToUpper(), ParameterDirection.Input);
cmd.Parameters.Add("count", OracleDbType.Int32).Direction
= ParameterDirection.ReturnValue);
cmd.ExecuteNonQuery();
conn.Close();
return Convert.ToInt32(cmd.Parameters["count"].Value);
}


Thanks.

 

[Sergioesp]

Problem solved, apparently ODP.NET uses a different approach. I
changed the command text and type and it worked fine.

OracleCommand cmd = new OracleCommand("begin :count :=
pkg_authentication.f_user_existsp_username); end;", conn);
cmd.CommandType = CommandType.Text;

Thanks.