Question:

I'm attempting to setup a small application to pull data on a weekly basis. I created the PL/SQL query itself in TOAD and it executes fine there when run manually. From there I wrote the following script for my VB application:

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

As you can see from the code I've tried several versions of the query language, but they all return ORA00911. I've checked for formatted single quotes in each of the cases, which is the only suggestion I've seen online that could have applied to my situation.

If anyone can point out what character(s) are causing trouble it would be much appreciated.

NOTE: For the record my dream scenario is that Format 2 is the one that makes it into use, much easier to read than Format 1 (which is otherwise the same code), and doesn't require extra VB processing like Formats 3 & 4.

Thanks in advance!

Collapsar is correct in the comments

"I'd expect the semicolons at the end of each of the query variants to be responsible. Drop them. – collapsar Feb 24 '15 at 23:44"

This completely solved my dynamic SQL problem from vb.net for 'Invalid Character'. Thanks!