SQL查询字符串中的“无效字符”(ORA-00911)

Question

我正在尝试设置一个小应用程序，以每周为基础拉取数据。我在TOAD中创建了PL/SQL查询本身，当手动运行时，它在那里执行得很好。在那里，我为我的VB应用程序编写了以下脚本：

Private Sub MainWin_Shown(sender As Object, e As EventArgs) Handles Me.Shown
    '
    Dim QTxt As String = ""
    Dim ConStr As String = "Data Source=b;User Id=my_user_name;Password=some_pass;"
    Dim scon As New Oracle.DataAccess.Client.OracleConnection(ConStr)
    Dim d As New DataStore
    Dim scmd As New Oracle.DataAccess.Client.OracleCommand
    Dim odr As Oracle.DataAccess.Client.OracleDataReader

    Me.Status.Text = Now() & " - Building the SQL executor"
    Me.Refresh()
    'Build the query executor
    Try
        scmd.CommandType = CommandType.Text
        scmd.Connection = scon
        'Format 1 for the PL/SQL query language taken from TOAD (SINGLE LINE FORMART)
        'QTxt = "select case   when (userhost like 'uss-09%' and userid in ('A', 'ASYSPUB')) then 'B'   when userid like 'NOE%' then 'Noe'   when ((userhost like  'uss01%' or userhost like 'uss02%') and userid in ('A','ASYSPUB')) then 'F'   when ((userid like '%_IU%' or userid like 'RPT%' or userid in ('EFRW', 'EMOPOR', 'EM_IU')) and userhost <> 'uss%') then 'I'   else 'Other'  end app_type, round(sum(sessioncpu/100), 1)  cpu_seconds, (sum(sessioncpu/100)/(119*1*60*60)*100) pct_of_cpu, trunc(nts#,'MI') RunDate from PSTAT.AUD$_A where nts# >= ((trunc(SYSDATE)-9))  and nts# < (trunc(SYSDATE)-2)  and l$t < (trunc(SYSDATE)-2) group by  case  when (userhost like 'uss-09%' and userid in ('A', 'ASYSPUB')) then 'B'  when userid like 'NOE%' then 'Noe'  when ((userhost like 'uss01%' or userhost like 'uss02%' ) and userid in ('A','ASYSPUB')) then 'F'  when ((userid like '%_IU%' or userid like 'RPT%' or userid in ('EFRW', 'EMOPOR', 'EM_IU')) and userhost <> 'uss%') then 'I'  else 'Other'  end, trunc(nts#,'MI') order by trunc(nts#,'MI'),1;"

        'Format 2 for the PL/SQL query language taken from TOAD (Wrapped as in TOAD)
        '            QTxt = "select case" & Chr(10) & _
        '        "when (userhost like 'uss-9%' and userid in ('A', 'ASYSPUB')) then 'B'" & Chr(10) & _
        '        "when userid like 'NOE%' then 'Noe'" & Chr(10) & _
        '        "when ((userhost like  'usst01%' or userhost like 'uss02%' ) and userid in ('A','ASYSPUB')) then 'F'" & Chr(10) & _
        '        "when ((userid like '%_IU%' or userid like 'RPT%' or userid in ('EFRW', 'EMOPOR', 'EM_IU')) and userhost <> 'uss%') then 'I'" & Chr(10) & _
        '        "else 'Other'" & Chr(10) & _
        '    "end app_type, round(sum(sessioncpu/100), 1)  cpu_seconds, (sum(sessioncpu/100)/(119*1*60*60)*100) pct_of_cpu, trunc(nts#,'MI') RunDate" & Chr(10) & _
        '"from PSTAT.AUD$_A" & Chr(10) & _
        '            "where nts# >= ((trunc(SYSDATE) - 9))" & Chr(10) & _
        '    "and nts# < (trunc(SYSDATE)-2)" & Chr(10) & _
        '    "and l$t < (trunc(SYSDATE)-2)" & Chr(10) & _
        '            "group by" & Chr(10) & _
        '    "case" & Chr(10) & _
        '    "when (userhost like 'uss-09%' and userid in ('A', 'ASYSPUB')) then 'B'" & Chr(10) & _
        '    "when userid like 'NOE%' then 'Noe'" & Chr(10) & _
        '    "when ((userhost like  'uss01%' or userhost like 'uss02%' ) and userid in ('A','ASYSPUB')) then 'F'" & Chr(10) & _
        '    "when ((userid like '%_IU%' or userid like 'RPT%' or userid in ('EFRW', 'EMOPOR', 'EM_IU')) and userhost <> 'uss%') then 'I'" & Chr(10) & _
        '    "else 'Other'" & Chr(10) & _
        '    "end, trunc(nts#,'MI') order by trunc(nts#,'MI'),1;"

        'FORMAT 3 for PL/SQL taken form TOAD (CASE STATEMENT REMOVED *** WILL NEED TO REWORK AS IN CODE LOGIC ***)
        QTxt = "SELECT userhost, userid, round(sum(sessioncpu/100), 1)  cpu_seconds, (sum(sessioncpu/100)/(119*1*60*60)*100) pct_of_cpu, trunc(nts#,'MI') RunDate " & _
            "From PSTAT.AUD$_A " & _
            "WHERE nts# >= ((trunc(SYSDATE) - 9)) " & _
            "   AND nts# < (trunc(SYSDATE)-2) " & _
            "   AND l$t < (trunc(SYSDATE)-2) " & _
            "GROUP BY trunc(nts#, 'MI') " & _
            "ORDER BY trunc(nts#, 'MI'), 1;"

        'Format 4 - SINGLE LINE WITH CASE STATEMENT REMOVED
        'QTxt = "SELECT userhost, userid, round(sum(sessioncpu/100), 1) cpu_seconds, (sum(sessioncpu/100)/(119*1*60*60)*100) pct_cpu, trunc(nts#, 'MI') RunDate FROM PSTAT.AUD$_A WHERE nts# >= (trunc(SYSDATE) - 9) AND nts# < (trunc(SYSDATE) - 2) AND logoff$time < (trunc(SYSDATE) - 2) GROUP BY trunc(nts#, 'MI') ORDER BY trunc(nts#, 'MI'), 1;"
        scmd.CommandText = QTxt
    Catch ex As Exception
        Me.Errors.Text = "An error occurred while building the SQL Executor. Details: " & ex.Message & Chr(10) & Chr(10) & Me.Errors.Text
        Exit Sub
    End Try

    Me.ProgBar.Step = 5
    Me.ProgBar.PerformStep()
    Me.Status.Text = Now() & " - Connecting to the database" & Chr(10) & Me.Status.Text
    Me.Refresh()
    Try
        'Open the connection
        scon.Open()
    Catch ex As Exception
        Me.Errors.Text = "An error occurred while opening the SQL connection. Details: " & ex.Message & Chr(10) & Chr(10) & Me.Errors.Text
        Exit Sub
    End Try

    Me.ProgBar.PerformStep()
    Me.Refresh()
    Me.Status.Text = Now() & " - Executing SQL statement and collecting results" & Chr(10) & Me.Status.Text
    'Use the reader to get the sql results
    Try
        odr = scmd.ExecuteReader
        If Not (TableFill(odr, d)) Then
            '    'Already captured the error at the point it occured, just clean up
            scmd.CommandText = Nothing
            scmd.Connection = Nothing
            scmd = Nothing
            odr = Nothing
            scon.Close()
            scon = Nothing
        End If 'else allow the macro to proceed

    Catch ex As Exception 'THIS IS THE CATCH THAT THE ERROR GOES THROUGH

        Me.Errors.Text = "Encountered an error while executing the query and capturing its results. Details: " & ex.Message & Chr(10) & Chr(10) & Me.Errors.Text
        Me.Errors.Text = Me.Errors.Text & Chr(10) & Chr(10) & QTxt
        Exit Sub
    End Try

    'Pass the SQL Reader to the Excel Module to create the XLSX
    Me.ProgBar.Step = 15
    Me.ProgBar.PerformStep()
    Me.Status.Text = Now() & " - Exporting the resultant data to an Excel file." & Chr(10) & Me.Status.Text
    Me.Refresh()
    Try
        If TableRO(d) Then
            Me.Status.Text = Now() & " - Successfully exported the query results. Please review any logged errors and then close this window." & Chr(10) & Me.Status.Text
            Me.ProgBar.Width = 100
        Else
            Me.Status.Text = Now() & " - Failed to export the query results. Please review the logged errors for further details." & Chr(10) & Me.Status.Text
        End If
    Catch ex As Exception
        Me.Errors.Text = "Encountered an error while reading out the SQL results. Details: " & ex.Message & Chr(10) & Chr(10) & Me.Errors.Text
    End Try

    'Clean up
    Try
        scmd.CommandText = Nothing
        scmd.Connection = Nothing
        scmd = Nothing
        scon = Nothing
        odr = Nothing
        scon.Close()
        d.DT.Clear()
        d = Nothing
    Catch ex As Exception
        Me.Errors.Text = "Encountered an error while cleaning up varibles. Details: " & ex.Message & Chr(10) & Chr(10) & Me.Errors.Text
    End Try
End Sub

正如您从代码中看到的，我已经尝试了查询语言的几个版本，但它们都返回ORA00911。我在每种情况下都检查了格式化的单引号，这是我在网上看到的唯一适用于我的情况的建议。

如果有人能指出是什么字符造成了麻烦，我们将不胜感激。

注意:对于记录，我梦想的场景是，格式2使其投入使用，比格式1(否则是相同的代码)更容易阅读，并且不需要像格式3和4那样的额外的VB处理。

提前感谢！

Answer 1

Collapsar在评论中是正确的

“我希望每个查询变量末尾的分号都会起作用。去掉它们。-- collapsar 24 '15 at 23:44”

这完全解决了我在vb.net中“无效字符”的动态SQL问题。谢谢!