-
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
-
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.
-
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.
-
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
-
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.
-
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
-
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) !
-
Originally Posted by A112Abarth
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|