C# + Pl/SQL Problem with out parameter in stored procedure

user_9963929 0
Viewed 1371

I have a stored procedure which looks like this:

CREATE OR REPLACE PROCEDURE
Get_Username
(
    p_Username IN user.USERNAME%TYPE,
    p_ReturningUsername OUT user.Username%TYPE
)
IS
BEGIN
    SELECT username INTO p_ReturningUsername FROM user WHERE p_Username = username;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_ReturningUsername := null;
END;

I've executed it from the database and it worked perfectly. When I execute it from c# app I don't get any value back.

public bool getUsername(string username)
    {
        OracleConnection connection = getConnection();
        OracleCommand oracleCommand = new OracleCommand("Get_Username", connection);
        oracleCommand.CommandType = CommandType.StoredProcedure;
        oracleCommand.Parameters.Add("p_ReturningUsername", OracleDbType.Varchar2).Direction = ParameterDirection.Output;
        oracleCommand.Parameters.Add("p_Username", OracleDbType.Varchar2).Value = username;

        try
        {
            oracleCommand.ExecuteNonQuery();
        }
        catch (OracleException ex)
        {
            MessageBox.Show("Exception Message: " + ex.Message);
            MessageBox.Show("Exception Source: " + ex.Source);
        }


        string tmp = oracleCommand.Parameters["p_ReturningUsername"].Value.ToString();
        connection.Close();
        return tmp == username;
    }

For a WPF registration window I need to check if the username is already taken. When I execute this code I receive an empty string when it has to be the username in the user table.

Please help me, thanks in advance.

plsql oracle sql c#
2 Answers

As @Codo says, add the line command.BindByName = true;, but also replace the line 

        oracleCommand.Parameters.Add("p_ReturningUsername", OracleDbType.Varchar2).Direction = ParameterDirection.Output;

with

        oracleCommand.Parameters.Add("p_ReturningUsername", OracleDbType.Varchar2, 100, null, ParameterDirection.Output);

Replace the number 100 with the number of characters in your username column.

We use this overload of the Add method so that we can specify the size of the output parameter. This allows the Oracle driver to make enough space to receive the value from the database. The null argument is for the initial value of the parameter, which we don't care about because this is an output parameter, but we have to specify it as there is no overload of the Add method that takes only the name, type, size and direction.

Pourquoi_Litytestdata_485030

Try this code (I've just swapped two lines):

public bool getUsername(string username)
    {
        OracleConnection connection = getConnection();
        OracleCommand oracleCommand = new OracleCommand("Get_Username", connection);
        oracleCommand.CommandType = CommandType.StoredProcedure;
        oracleCommand.Parameters.Add("p_Username", OracleDbType.Varchar2).Value = username;
        oracleCommand.Parameters.Add("p_ReturningUsername", OracleDbType.Varchar2).Direction = ParameterDirection.Output;

        try
        {
            oracleCommand.ExecuteNonQuery();
        }
        catch (OracleException ex)
        {
            MessageBox.Show("Exception Message: " + ex.Message);
            MessageBox.Show("Exception Source: " + ex.Source);
        }


        string tmp = oracleCommand.Parameters["p_ReturningUsername"].Value.ToString();
        connection.Close();
        return tmp == username;
    }

Or add:

oracleCommand.BindByName = true;

By default, the OracleCommand class binds parameters by order, not by name.

user_4133370
Related