0 Replies Latest reply on Dec 19, 2013 12:33 PM by di*427998*ka

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

    di*427998*ka

      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 For example "WW2 ",

      This was working fine over the years using the Microsoft-Ado Provider MSDAORA ( with Excel 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 ParameterValue 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 ?

       

      I am aware of the differences between the two character types VARCHAR2 / CHAR ( 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 ).

       

      Thanks.

       

      Best regards

       

      Dirk