CODEWITHSUNDEEP
c#oracle-databasedevartnamed-parameters
Marc Wittmann
Marc Wittmann

Reputation: 2671

Oracle Parameters: expects padded value for fixed char length

How do you efficiently use OracleCommand and Parameters to get data from an Oracle DB when you have a fixed length char colum and don`t know how big the column is?

Let`s assume we want to get an ID named IID which is a char (5 bytes) fixed.

 OracleCommand myCommand1;
 myCommand1.CommandText = "SELECT * FROM IDS WHERE IID = :IID";
 myCommand1.Parameters.AddWithValue("IID", "1234");

Would NOT return an item but

 myCommand1.Parameters.AddWithValue("IID", "1234 ");

Would because it matches the 5 bytes in the database

You could also specify the space

myCommand1.Parameters.Add("IID", OracleDbType.Char, 5).Value = "1234";

But in my case just pretend that the programmer does not always know the exact amount of the char size defined in the database (if it makes sense or not). I use devart but I think this is more of a general issue. How could you pass the "1234" parameter without padding?

Thanks in advance

Upvotes: 0

Views: 265

Answers (1)

MT0
MT0

Reputation: 168740

From the Data Type Comparison Rules documentation:

Blank-Padded and Nonpadded Comparison Semantics

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

With nonpadded semantics, Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2.

If you have two CHAR values then Oracle should use Blank-Padded Comparison Semantics and will add blanks to the strings until they are equal length.

If you have one-or-more VARCHAR2 values then Oracle will use Non-Padded Comparison Semantics.

This means you should be able to pass a CHAR of any length and it will be compared at the appropriate length:

string iid = "1234";
myCommand1.Parameters.Add("IID", OracleDbType.Char, iid.Length).Value = iid;

Note: If you are having this problem then it suggests that you should not be storing strings as a CHAR and should be using VARCHAR2 instead.

Upvotes: 1

Related Questions