如何解决VBA从Oracle数据库按标题查询时的ORA-01861错误？

我的VBA代码在输入标题名称「711_23PS」时能正常运行，但输入其他标题会抛出「ORA-01861: literal does not match format string」错误。这段代码用于通过Oracle数据库的TITLE列标题名称获取数据，原代码如下：

Sub RetrieveSpecificTitleFromOracle()
    ' Declare variables
    Dim conn As Object ' Connection
    Dim rs As Object ' Recordset
    Dim strSql As String ' SQL query
    Dim i As Integer ' Column counter
    Dim titleName As String ' Title name
    Dim colCount As Integer ' Number of columns

    ' Set up Oracle connection
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=DATASOURCEXXX;User ID=USERIDXXX;Password=PASSWORDXXX;"
    conn.Open

    ' Get the title name from the user
    titleName = InputBox("Enter the title name:")
    If titleName = "" Then Exit Sub ' If the user cancels, exit the macro

    ' Set up SQL query to retrieve specific row by title name
    strSql = "SELECT * FROM FAR_OWNER.FAR_VX_REQUESTS WHERE TITLE = '" & titleName & "'"

    ' Execute the query
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSql, conn

    ' Check if any data is returned
    If Not rs.EOF Then
        ' Get the number of columns
        colCount = rs.Fields.Count

        ' Copy data to Excel sheet
        For i = 1 To colCount
            Cells(1, i).Value = rs.Fields(i - 1).Name ' Copy column headers
            Cells(2, i).Value = rs.Fields(i - 1).Value ' Copy data from the selected row
        Next i

        ' Auto-fit columns
        Columns.AutoFit
    Else
        MsgBox "No data found for the specified title name."
    End If

    ' Clean up
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing

    ' Inform user about the completion
    MsgBox "Data retrieval completed."
End Sub

问题原因 #

• 直接拼接字符串生成SQL语句，若标题包含单引号等特殊字符（比如O'Conner），会破坏SQL语法结构，导致Oracle解析错误
• ORA-01861错误看似是日期格式问题，实际是非法字符破坏SQL后，Oracle将部分内容误判为日期字面量，引发格式不匹配
• 「711_23PS」能正常运行是因为它不含任何会干扰SQL结构的特殊字符

解决方案 #

使用参数化查询，通过ADODB.Command对象绑定参数，彻底避免字符串拼接的风险。修改后的代码如下：

Sub RetrieveSpecificTitleFromOracle()
    ' Declare variables
    Dim conn As Object ' Connection
    Dim rs As Object ' Recordset
    Dim cmd As Object ' Command object for parameterized query
    Dim strSql As String ' SQL query
    Dim i As Integer ' Column counter
    Dim titleName As String ' Title name
    Dim colCount As Integer ' Number of columns

    ' Set up Oracle connection
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=DATASOURCEXXX;User ID=USERIDXXX;Password=PASSWORDXXX;"
    conn.Open

    ' Get the title name from the user
    titleName = InputBox("Enter the title name:")
    If titleName = "" Then Exit Sub ' If the user cancels, exit the macro

    ' Set up parameterized SQL query
    strSql = "SELECT * FROM FAR_OWNER.FAR_VX_REQUESTS WHERE TITLE = :TitleParam"
    
    ' Create command object
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandText = strSql
    cmd.CommandType = 1 ' adCmdText

    ' Add and set parameter value
    cmd.Parameters.Append cmd.CreateParameter("TitleParam", 200, 1, 255, titleName) ' 200 = adVarChar, 1 = adParamInput

    ' Execute query and get recordset
    Set rs = cmd.Execute

    ' Check if any data is returned
    If Not rs.EOF Then
        ' Get the number of columns
        colCount = rs.Fields.Count

        ' Copy data to Excel sheet
        For i = 1 To colCount
            Cells(1, i).Value = rs.Fields(i - 1).Name ' Copy column headers
            Cells(2, i).Value = rs.Fields(i - 1).Value ' Copy data from the selected row
        Next i

        ' Auto-fit columns
        Columns.AutoFit
    Else
        MsgBox "No data found for the specified title name."
    End If

    ' Clean up
    rs.Close
    conn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set conn = Nothing

    ' Inform user about the completion
    MsgBox "Data retrieval completed."
End Sub

关键修改点 #

• 替换字符串拼接的SQL为参数化查询，用:TitleParam作为占位符
• 使用ADODB.Command对象绑定参数，自动处理特殊字符转义
• 指定参数类型（adVarChar）和长度，确保Oracle正确解析参数

内容的提问来源于stack exchange，提问作者Aaron Delos Angeles