Oracle ORAOLEDB Provider removes trailing blanks in parameter values of Type IN CHAR
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

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

  1. #1
    Join Date
    Dec 2012
    Posts
    8

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    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

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by A112Abarth View Post
    ...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.
    Last edited by PAVB; 12-18-2013 at 09:19 AM. Reason: typo
    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.

  4. #4
    Join Date
    Dec 2012
    Posts
    8
    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 )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width