Why doesn't my C# code get a return value from my Oracle function?

Question

I am trying to check if a user is in my database, my return value from my function is either a 't' or an 'f'. as below:

CREATE OR REPLACE FUNCTION LOGIN
    (p_Naam in varchar2
    ,p_Wachtwoord in varchar2)
    return varchar2
is
    v_count number;
BEGIN
    select count(*) into v_count
    from Lid
    where Naam = p_Naam 
    and Wachtwoord = p_Wachtwoord;
    if v_count > 0 then
        return 't';
    end if;
    return 'f';
END LOGIN;

now i call this function with my C# code as below:

public bool LogIn(string gebruikersnaam, string wachtwoord)
    {
        string s;

        var cmd = new OracleCommand
        {
            Connection = conn,
            CommandText = "Login",
            CommandType = CommandType.StoredProcedure
        };

        cmd.Parameters.Add("p_Naam", OracleDbType.Varchar2).Value = gebruikersnaam;
        cmd.Parameters.Add("p_Wachtwoord", OracleDbType.Varchar2).Value = wachtwoord;
        cmd.Parameters.Add("return_value", OracleDbType.Varchar2, ParameterDirection.ReturnValue);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            s = cmd.Parameters["return_value"].Value.ToString();
        }
        finally
        {
            conn.Close();
        }

        return s == "t";
    }

when i try this funcion within my oracle developer i get an output. only in my C# code, s always comes out as ""

in my sql developer the following gives me 't'

BEGIN
  dbms_output.put_line(LOGIN('Willem Koonings', 'willem'));
END;

ParameterDirection.ReturnValue could only return a numeric value, not a string. (At least in Sql Server) try to change your proc to return an integer and your parameters collection to receive an integer. — Steve
– Steve, Commented Jun 17, 2014 at 13:07
Try changing the statement that adds the "return_value" parameter to cmd.Parameters.Add("return_value", OracleType.VarChar2, 1).Direction = ParameterDirection.ReturnValue;. The third parameter to Add, in this case, is supposed to be the size of the column - in this case you're only returning a single character so it should be 1. ParameterDirection is a property of the parameter. Share and enjoy. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jun 17, 2014 at 13:08

flindeberg · Accepted Answer · 2014-06-17 13:11:01Z

1

I haven't been working with OracleCommands for a while, but i would suggest this change:

CommandText = "Login(:p_Naam, :p_Wachtwoord)";

The long solution which I bets works (change type to text though):

CommandText = "select Login(:p_Naam, :p_Wachtwoord) return_value from dual";

answered Jun 17, 2014 at 13:11

flindeberg

5,0471 gold badge27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nyerguds Over a year ago

Yes and no... just changing it like that in his current code won't do any good; it's still going to get executed as stored procedure with CommandType = CommandType.StoredProcedure. However, there's certainly a case to be made for simply executing it as a select; it would most likely work perfectly fine for a simple function like that.

Hambone · Accepted Answer · 2014-06-19 16:01:57Z

1

When you have this in your code:

s = cmd.Parameters["return_value"].Value.ToString();

It implies, to me, that your command had somethink akin to this:

insert into test (user_name, create_date)
values (:USERNAME, sysdate)
returning user_id into :return_value

I've never seen a stored procedure use a "returning" as such. That doesn't automatically mean it can't be done, but it doesn't seem consistent with how I've used it and seen it used.

Since you are running a stored proc that returns a scalar value, this would probably suit you better:

string result = (string)cmd.ExecuteScalar();

answered Jun 19, 2014 at 16:01

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

1 Comment

Nyerguds Over a year ago

Oracle has advanced programming with PL/SQL. So ya, it has functions.

Dominic Cotton · Accepted Answer · 2015-12-01 09:13:06Z

1

You need to place the "return value" parameter first in the index, as far as i can tell from working with Oracle it doesn't pass the names of the parameters to the Oracle call, it is absolute placing.

Hope this solves your problems :)

Dom

answered Dec 1, 2015 at 9:13

Dominic Cotton

81912 silver badges34 bronze badges

Comments

Steve · Accepted Answer · 2014-06-17 13:14:46Z

ParameterDirection.ReturnValue could return only a numeric value, not a string or single char.
(At least in Sql Server). Try to change your function to return an integer and your parameters collection to receive an integer. (I don't have Oracle to test, please correct me if there are some syntax errors)

See this reference (for Sql Server)

CREATE OR REPLACE FUNCTION LOGIN
    (p_Naam in varchar2
    ,p_Wachtwoord in varchar2)
    return INTEGER 
is
    v_count number;
BEGIN
    select count(*) into v_count
    from Lid
    where Naam = p_Naam 
    and Wachtwoord = p_Wachtwoord;
    if v_count > 0 then
        return 1;
    end if;
    return 0;
END LOGIN;

....
cmd.Parameters.Add("p_Naam", OracleDbType.Varchar2).Value = gebruikersnaam;
cmd.Parameters.Add("p_Wachtwoord", OracleDbType.Varchar2).Value = wachtwoord;
cmd.Parameters.Add("return_value", OracleDbType.Int32, ParameterDirection.ReturnValue);
....

int result = Convert.ToInt32(cmd.Parameters["return_value"].Value);
if(result == 0)
   // fail
else
   // success...

rajGoogles4Code · Accepted Answer · 2014-06-17 13:24:12Z

0

Instead of 'Return' statement why don't you declare a out parameter .Let me know if iam not clear

answered Jun 17, 2014 at 13:24

rajGoogles4Code

113 bronze badges

Comments

Dave · Accepted Answer · 2018-10-15 09:29:23Z

Just found out this and tested with VS2017 Community edition + 11g.

OracleCommand chkCmd = null;
try
{
    chkCmd = new OracleCommand();
    chkCmd.CommandText = "login";
    chkCmd.CommandType = CommandType.StoredProcedure;

    chkCmd.Connection = conn;
    OracleParameter mobParam1 = new OracleParameter("p_Naam", OracleDbType.Varchar2, 2000);
    mobParam1.Direction = ParameterDirection.Input;
    mobParam1.Value = gebruikersnaam;
    OracleParameter mobParam2 = new OracleParameter("p_Wachtwoord", OracleDbType.Varchar2, 2000);
    mobParam2.Direction = ParameterDirection.Input;
    mobParam2.Value = wachtwoord;
    OracleParameter retValue = new OracleParameter("returnVal", OracleDbType.Varchar2, 2000);
    retValue.Direction = ParameterDirection.ReturnValue;


    chkCmd.Parameters.Clear();
    chkCmd.Parameters.Add(retValue);
    chkCmd.Parameters.Add(mobParam1);
    chkCmd.Parameters.Add(mobParam2);
    con.Open();
    chkCmd.ExecuteScalar();
    string retmsg = Convert.ToString(retValue.Value);


    return retmsg=="t";
}
finally 
{
    con.Close();
    chkCmd.Dispose();
    con.Dispose();
}

Thnesko · Accepted Answer · 2019-03-01 15:24:00Z

0

Make Return_Value your first parameter:

cmd.Parameters.Add("Return_Value", OracleDbType.Boolean, ParameterDirection.ReturnValue);

answered Mar 1, 2019 at 15:24

Thnesko

1082 silver badges4 bronze badges

Collectives™ on Stack Overflow

Why doesn't my C# code get a return value from my Oracle function?

7 Answers 7

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related