Oracle ORAOLEDB Provider removes trailing blanks in parameter values of Type IN CHAR

Hi,

I often use StoredProcedures with Ref_Cursor parameters to read data from an Oracle 11g DB.
Because we use VARCHAR2 columns in our tables, we have to pad blanks for an exact match, the Input Parameter has to contain the blanks for example "WW2 ",
This was working fine over the years using the Microsoft-Ado Provider MSDAORA ( with Excel 2003/10, VBA ).
Now we want to use the Oracle ORAOLEDB Provider. In our tests all resultsets were empty(rs.EOF/rs.BOF)
The reason is, that inside the StoredProcedure the Parameter value was trimmed ( "WW2" ), and so the Where-Clause doesn't match.

Is it possible to change this behaviour( bug or bad feature ? ) by setting a property of an object , in registry or somewhere else ?

Thanks.

Best regards

Dirk

A VARCHAR2 does not contain trailing blanks. If you wanted that you would use CHAR instead. It would seem that you MSDAORA driver was not trimming blanks, or was padding variables with blanks you adjusted your code to be wrong. It seems that you need to change your code to treat VARCHAR2 as a right trimmed variable, hence trim the value in .NET or whatever you code in.

Quote:

---

Originally Posted by A112Abarth

...bug or bad feature ?

---

As Gandolf pointed out, this is neither a bug nor a bad feature, this is exactly how it is supposed to work.
If padding has to be added, rpad() would be most probably the easiest and cleanest way to do it.

Full agreement on the subject VARCHAR2. I am also aware of the differences between the two character types( blankpadded/no blankpadded compare ).
But the parameters of the stored procedure is CHAR (fixed length). and in this case strings are usually padded with blanks, and so I think, that no component has to modify a fixed length object, especially if the length of the parameter is a hard setting in vba code( Set Param = SqlCmd.CreateParameter("strAnlage", adChar, adParamInput, 4) , and in PL/SQL : strAnlage IN CHAR )