11795

“Invalid Character” in SQL query string (ORA-00911)

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!

Answer1:

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!

Recommend

  • “Invalid Character” in SQL query string (ORA-00911)
  • C++ same variable act as int and float
  • oracle query to group date difference by hour
  • passing date range parameters from a vb.net windows application
  • Writing to file and mkdir race conditions C
  • Visual Studio SSRS RDL Files creating a new file (backup) when opened
  • ASP.NET MapPath to the root of the Solution
  • Oracle: How to find date of last Friday of the month
  • ORA-04098 is invalid and failed re-validation
  • Passing Values to a Crystal Report
  • System Uptime in Delphi 2009
  • How can I cut a string after X characters?
  • Winform root directory path.How to again!
  • How to partition over months and group months in a quarter
  • Where to see the Dockerfile for a docker image?
  • Automatic string resources translate
  • Count call types by week, grouped by type, to identify trends
  • lag function to get the last different value(redshift)
  • Oracle get all dates in a month
  • Calculation Error in Crystal Report Fomula
  • Generate Month Name, Day name, Week number and Day number between two dates
  • How to Get Total Page Count for a Report Using CrystalReportViewer?
  • Adding links to an array with jquery
  • How to load space separate file into pandas dataframe?
  • how to replace string in SpEL expression?
  • Submitting a Hadoop job
  • Vagrant rsync error: Error: Could not create directory '/home/Eric/.ssh'
  • Change an a tag attribute in JavaScript based on screen width
  • costura.fody for a dll that references another dll
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal