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

Question

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.

Luke Woodward · Accepted Answer · 2018-11-14 19:40:33Z

2

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.

answered Nov 14, 2018 at 19:40

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

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

1 Comment

BrainBox Over a year ago

Thanks a lot it worked, and now I understand how it works properly.

Codo · Accepted Answer · 2018-11-14 17:56:09Z

0

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.

answered Nov 14, 2018 at 17:56

Codo

79.7k18 gold badges176 silver badges218 bronze badges

1 Comment

BrainBox Over a year ago

That was my original code. I swapped it because I recive an exception executing this code: Exception Message: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "W1O67X.GET_USERNAME", line 9 ORA-06512: at line 1 Exception Source: Oracle Data Provider for .NET, Managed Driver And I recive the same for the BindByName

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related