DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Oracle OLEDB Provider adds blanks

Hybrid View

  1. #1
    Join Date
    Dec 2012
    Posts
    8

    Oracle OLEDB Provider adds blanks

    Hi,

    as the this small prodedure demonstrates, the Oracle OLEDB Provider attaches some blanks to the constant read by the Sql-statement.
    On the opposite the Microsoft Provider MSDAORA does what normally would be expected, it only prints out 1 single character.
    What is the problem, is there something to configure ? The blankadding behaviour causes unnecessary problems.

    The Oracle OLEDB Provider seems to have some problem with blanks, sometimes it adds some, sometimes it removes some.

    Thanx.

    Best regards

    Dirk

    Output Oracle provider ( with the crazy blanks following the F ) :
    Used Provider OraOLEDB.Oracle.1
    AttributeName : MARKER Value : -F -
    AttributeName : TRIMMED_MARKER Value : -F-

    Output using the Microsft Provider :
    Used Provider : MSDAORA.1
    AttributeName : MARKER Value : -F-
    AttributeName : TRIMMED_MARKER Value : -F-

    Demo Procedure :

    Sub Show_Blank_Problem()

    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim i As Integer
    Dim ConnectString As String

    ConnectString = "Provider=MSDAORA; Data Source=MyOraDB;"
    ' ConnectString = "Provider=OraOLEDB.Oracle.1;Data Source=MyOraDB;"

    Set OraConnect = New ADODB.Connection

    OraConnect.Open ConnectString, "scott", "tiger"
    strSQL = " select 'F' as Marker, Trim( 'F' ) as Trimmed_Marker from Dual "
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open strSQL, OraConnect, adOpenStatic, adLockReadOnly

    If rs.BOF And rs.EOF Then
    Debug.Print "No Data Found!"
    rs.Close
    Exit Sub
    End If



    Debug.Print "Used Provider : " & OraConnect.Provider

    Do While Not rs.EOF
    For i = 0 To (rs.Fields.Count - 1)
    Debug.Print "AttributeName : " & CStr(rs.Fields(i).Name) & " Value : " & "-" & rs.Fields(i) & "-"
    Next i

    rs.MoveNext

    Loop

    rs.Close

    End Sub

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Are you using Visual Basic .NET? There is likely a fundamental difference in how the two drivers handle unicode character sets.
    What version of Oracle OLEDB driver and what version of MSDAORA driver are you using? Sometimes trying a newer driver can help.

  3. #3
    Join Date
    Dec 2012
    Posts
    8
    I'm using :
    Win 64 professional
    Office 2010 32bit , Excel VBA
    MDAC 2.8 SP1 mit MS Ado 2.8 ( "c:\Program Files (x86)\Common Files\System\ado\msado28.tlb" )
    The reference on the Oracle driver OraOLEDB11.dll ( 11.2.0.2 ) exists, but is not neccessary.

    Oracle DB :
    PARAMETER VALUE
    NLS_CHARACTERSET WE8MSWIN1252
    Last edited by A112Abarth; 01-02-2014 at 11:39 AM.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    So it works with one driver but not the other? If so use the driver that works.
    MDAC 2.8 should support unicode. There might be a parameter in the odbc
    admin tool that you need to set.

    http://en.wikipedia.org/wiki/Microso...ess_Components

  5. #5
    Join Date
    Dec 2012
    Posts
    8
    We do'nt use Unicode, the named case works with Microsoft, but not with Oracle. As in an other thread mentioned the oracle driver removes blanks, the Microsoft driver doesn't.
    But with the oracle driver you can use database links, with Microsoft this is not possible. The same for using more than one ref_cursor parameter in a stored procedure.
    And as far as I know, the Microsoft Provider is old, and gets no further development, therefore we want to use the oracle driver. But the blankhandling seems to be buggy or only configurable at a hidden place.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    So the Oracle ODBC driver thinks it is getting unicode characters and is padding the results with extra characters.
    You can try a newer microsoft ODBC driver, which you can probably download from microsoft.com, or you can try
    an older Oracle ODBC driver.

    You may also want to verify that your database is a single byte character set.

    Code:
    SQL> select * from V$NLS_PARAMETERS where PARAMETER='NLS_CHARACTERSET';
    
    PARAMETER                      VALUE
    ------------------------------ ------------------------------
    NLS_CHARACTERSET               AL32UTF8
    
    Elapsed: 00:00:00.00

  7. #7
    Join Date
    Dec 2012
    Posts
    8
    We use WE8MSWIN1252 as NLS_CHARACTERSET.

    I think the Oracle driver is simply buggy in handlig spaces :

    Driver gets one character from the Database( in sqlplus for example ) and it adds spaces. Why 32 and not 31 or 3 or 15 ?
    Driver gets from Client a string 'abc ' and removes the blank, although the parameter is defined as fixed length string CHAR(4) !

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by A112Abarth View Post
    I think the Oracle driver is simply buggy in handlig spaces
    Why not open a SR a.k.a. TAR with Oracle?
    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.

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I had similar issues with Linux and the heterogeneous gateway. We ended up upgrading from Redhat 5 to Redhat 6,
    whcih meant that there was a new driver available. the new driver worked perfectly. hence if you can upgrade to a
    new driver, it might also work fine.

  10. #10
    Join Date
    Dec 2012
    Posts
    8
    I try to get the newest driver. Do I have to install the complete newer Oracle Client or ist it possible only to install a newer OLEDB/ODBC ?
    If it then doesn't work correctly we will open an Oracle SR.

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