CODEWITHSUNDEEP
vbaoracle-databaseexcelcyrillic
realsnad
realsnad

Reputation: 45

Cyrillic letter displaying as question marks in VBA from Oracle

We've got a problem displaying Cyrillic letters from Clob field (Oracle) on Excel spreadsheet using ADODB.RecordSet (MS ActiveX Data Objects 2.8 Library).

As an example, I created a table with one Clob field. I inserted just one row with Cyrillic letters and am trying to show the value on Excel spreadsheet. However, the text shows as ???? both in Msgbox and in the cell. This happens only while getting values from Clob fields. It works fine if we query them from varchar. I've tried on 11.2.0.2.0 and 12.2.0.1.0. The behavior is the same. What could we do in order to fix this?

NLS_CHARACTERSET in db is AL32UTF8.

The VBA code is as follows:

Private Sub UnloadReportBtn_Click()
    Dim RecordSet As ADODB.RecordSet

    Set RecordSet = getTestClob

    While RecordSet.EOF = False
        MsgBox RecordSet.Fields("TEST1")

        Cells(7, 7) = RecordSet.Fields("TEST1")

        RecordSet.MoveNext
    Wend
End Sub

Public Function getTestClob()
    Dim Query As String

    Query = "SELECT TEST1 FROM TEST_CLOB"

    Set getTestClob = getRecordSet(Query)
End Function

Public Function getRecordSet(Query As String) As ADODB.RecordSet
    Dim SQLCommand As ADODB.Command

    Dim RecordSet As ADODB.RecordSet

    Set SQLCommand = New ADODB.Command
    Set SQLCommand.ActiveConnection = Connection

    SQLCommand.CommandText = Query
    SQLCommand.CommandType = adCmdText
    SQLCommand.CommandTimeout = 0

    Set getRecordSet = SQLCommand.execute
End Function

Upvotes: 2

Views: 1991

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59553

Set your NLS_LANG value to a character set which supports Cyrillic characters, e.g. CL8MSWIN1251 or AL32UTF8.

You can do this by Environment variable, for example

SET NLS_LANG=AMERICAN_RUSSIA.CL8MSWIN1251

or in your Registry at HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 32 bit), resp. HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG for 64-bit.

See also OdbcConnection returning Chinese Characters as "?" for more details.

Upvotes: 2

Related Questions