Oracle ORAOLEDB Provider removes trailing blanks in parameter values of Type IN CHAR
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 ?
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.
this space intentionally left blank
As Gandolf pointed out, this is neither a bug nor a bad feature, this is exactly how it is supposed to work.
Originally Posted by A112Abarth
If padding has to be added, rpad() would be most probably the easiest and cleanest way to do it.
Last edited by PAVB; 12-18-2013 at 09:19 AM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
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 )
Click Here to Expand Forum to Full Width