OracleCommand Parameter Varchar2 paradoxon ;-)

Question

i have an OracleDB with entries:

1, test, test
2, test, test

Id NUMBER(8,0)
Firstname (CHAR(30 BYTE))
Secondname CHAR(30 BYTE)

And now i have this lines of code to select this entries:

string query = @"SELECT ID,FIRSTNAME,LASTNAME FROM persdata 
                 WHERE (FIRSTNAME = 'test')";
var command = new OracleCommand(query, dbConnection) 
              { CommandType = CommandType.Text, BindByName = true };

IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    int someColumn = reader.GetInt32(reader.GetOrdinal("ID"));
    string anotherColumn = reader.GetString(reader.GetOrdinal("VORNAME"));
    string thirdColumn = reader.GetString(reader.GetOrdinal("NACHNAME"));
    Console.WriteLine(
        String.Format("{0}: {1}, {2}", someColumn, anotherColumn, thirdColumn)
    );
}

This Works fine but if i´m using parameters like:

string query = @"SELECT ID,FIRSTNAME,LASTNAME FROM persdata 
                 WHERE (FIRSTNAME = :param)";
var command = new OracleCommand(query, dbConnection) 
              { CommandType = CommandType.Text, BindByName = true };

command.Parameters.Add(":param", OracleDbType.Varchar2).Value = "test";

IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    int someColumn = reader.GetInt32(reader.GetOrdinal("ID"));
    string anotherColumn = reader.GetString(reader.GetOrdinal("FIRSTNAME"));
    string thirdColumn = reader.GetString(reader.GetOrdinal("LASTNAME"));
    Console.WriteLine(
        String.Format("{0}: {1}, {2}", someColumn, anotherColumn, thirdColumn)
    );
}

I don´t get anything! BUT if i set the ParameterValue like:

command.Parameters.Add(":param", OracleDbType.Varchar2).Value = "test                          ";

I get this entries o.O Is that the usal way, that i have to fill the parameters? But why the direct way doesn´t need it?

So is the right way to get the Column size and fill the parameter value? or am I doing it wrong?

Thanks :-)

Answer 1

I think your problem it's in the column type.

As explained here What is the major difference between Varchar2 and char , the CHAR(#) type it's meant to store fixed lenght strings. In your code, you are casting the parameter to a varchar2 type.

command.Parameters.Add(":param", OracleDbType.Varchar2).Value = "test";

try cast it to a OracleDbType.Char instead (or make the column a Varchar2)