0

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

Marc Wittmann
  • 2,286
  • 2
  • 28
  • 41
  • You should never pad. If you pad then when you fetch the data you would have to remove the padding. – jdweng Jun 26 '23 at 12:57
  • If its not of fixed length why is it a fixed length column? And you shouldn't let anyone allow to write sql unsupervised against an unknown/undefined/undocumented model. – Ralf Jun 26 '23 at 13:09
  • yes, true, and I don't want to pad anyway. It's all about ten thousands of lines of code of an older apllication which connects to a (documented) database to now use named parameter for security reasons. Everything works and though it would be better to use the exact definitions no one would pay for the extra work of an application that will have not other benefit than better documented connection code. I would rather do it differently. – Marc Wittmann Jun 26 '23 at 14:09

1 Answers1

1

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thank you for the detailed answer, really appreciated. You are correct. In general this should work. In my case I used the another parameter Name than stated in the question. I used "KontoId" as the parameter name and sql place holder :KontoId (NOT value) and nothing was returned if I didn`t use paddings. After changing to "Konto" it worked. I don't have any idea why. I thought it might have been the reserved word "id" but this is not the case. Do you have any insights on that, this should not happen at all according to documentation – Marc Wittmann Jun 27 '23 at 12:37