I've got a canned function in a class library to run a SQL query based on passed in query text. It works fine in most cases, but results in the error referenced in the title for a particular SQL query I'm trying to automate through it.
The entirety of the function from the class library:
''' <summary>
''' Attempts to execute the query provided and returns a datatable with the results (check for column ErrMsg to check for errors)
''' </summary>
''' <param name="FName">The name of the method requesting the query be executed</param>
''' <param name="path">The full file path to log any errors to</param>
''' <param name="QTxt">The query text to execute</param>
''' <param name="Close">Boolean to indicate whether the connection should be closed after the query is completed</param>
''' <param name="DB">The database to connect to (in case no connection is currently established)</param>
''' <param name="UName">The username to connect with (in case no connection is currently established)</param>
''' <param name="PWord">The password to connect with (in case no connection is currently established)</param>
''' <returns>A Data Table with the results of the query (on error one row and column added to return error message)</returns>
''' <remarks></remarks>
Public Function RunQuery(ByVal FName As String, ByVal path As String, ByVal QTxt As String, ByVal Close As Boolean, ByVal DB As String, ByVal UName As String, _
ByVal PWord As String) As Data.DataTable
'
Dim dt As New Data.DataTable
Dim oda As New Oracle.DataAccess.Client.OracleDataAdapter
Dim cmd As New Oracle.DataAccess.Client.OracleCommand
'Establish &/or Open connection as needed
Try
'Connection established?
If IsNothing(scon) Then 'scon is an Oracle connection variable associated with the class this method is part of
EstablishCon(DB, UName, PWord, path, FName)
End If
'Is the connection open?
If scon.State <> ConnectionState.Open Then
scon.Open()
End If
Catch ex As Exception
'Capture the base error
Dim msg As String = "Encountered an error while attempting to create and/or open the database connection to run the query for " & FName & "."
'Log the error with details (EL is an error logging class from the same library)
EL.AddErr(msg & " Error Details: " & ex.Message, path)
'Put the error into the data table for return, so that we can handle it on the other end
dt.Columns.Add("ErrMsg")
Dim r As DataRow
r = dt.NewRow
r.Item(0) = msg
'Exit
Return dt
Exit Function
End Try
'Build the command
Try
cmd.Connection = scon 'CMD is an Oracle command variable on the class
cmd.CommandText = QTxt
Catch ex As Exception
'Capture the base error
Dim msg As String = "Encountered an error while attempting to create the command to run the query for " & FName & "."
'Log the error with details
EL.AddErr(msg & " Error Details: " & ex.Message, path)
'Put the error into the data table for return, so that we can handle it on the other end
dt.Columns.Add("ErrMsg")
Dim r As DataRow
r = dt.NewRow
r.Item(0) = msg
dt.Rows.Add(r)
'Exit
Return dt
Exit Function
End Try
'Build the data adapter & Execute
Try
oda.SelectCommand = cmd 'oda is an Oracle Data Access variable
oda.Fill(dt) 'This is the line throwing the exception
Catch ex As Exception
'Capture the base error
Dim msg As String = "Encountered an error while attempting to execute the query for " & FName & "."
'Log the error with details
EL.AddErr(msg & " Error Details: " & ex.Message, path)
'Clear dt to prevent bad/partial data from being returned
dt.Clear()
Dim cl As New List(Of String)
For Each c As DataColumn In dt.Columns
cl.Add(c.ColumnName)
Next
For Each c As String In cl
dt.Columns.Remove(c)
Next
'Put the error into the data table for return, so that we can handle it on the other end
dt.Columns.Add("ErrMsg")
Dim r As DataRow
r = dt.NewRow
r.Item(0) = msg
dt.Rows.Add(r)
End Try
'Clean up
Try
oda.SelectCommand = Nothing
oda.Dispose()
oda = Nothing
cmd.Connection = Nothing
cmd.CommandText = Nothing
cmd.Dispose()
cmd = Nothing
If Close Then
scon.Close()
scon.ConnectionString = Nothing
scon.Dispose()
scon = Nothing
End If
Catch ex As Exception
EL.AddErr("Encountered an error while cleaning up from the query execution for " & FName & ". Error Details: " & ex.Message, path)
End Try
Return dt
End Function
The SQL being passed in (blank lines added for readability):
SELECT to_date('12-29-2013', 'MM-DD-YYYY') + to_Char(trunc(fcr.Actual_Start_Date) - to_date('12-29-2013', 'MM-DD-YYYY') -
to_char(fcr.Actual_Start_Date + 1, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''') + 1) SWeek,
to_date('12-29-2013', 'MM-DD-YYYY') + to_Char(trunc(fcr.Actual_Start_Date) - to_date('12-29-2013', 'MM-DD-YYYY') -
to_char(fcr.Actual_Start_Date + 1, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''') + 7) EWeek,
fcp.concurrent_program_name PName, NVL(ro.ou_name, 'system') OU, NULL BGrp,
count(*) total_cr_executions,sum(round(((fcr.actual_completion_date-fcr.actual_start_date)*1440),2)) TRtM,
((sum(round(((fcr.actual_completion_date-fcr.actual_start_date)*1440),2)))/count(*)) ARtM,
sum(round(((fcr.actual_start_date-fcr.requested_start_date)*1440),2)) TWtM,
((sum(round(((fcr.actual_start_date-fcr.requested_start_date)*1440),2)))/count(*)) AWtM
FROM hotsosp.XXFND_RESPONSIBILITY_OU ro,HOTSOSP.HWC_FND_CONCUR_PROGRAMS fcp,HOTSOSP.HWC_FND_CONCUR_REQUESTS fcr
WHERE ro.responsibility_id (+) = fcr.responsibility_id AND ro.application_id (+) = fcr.responsibility_application_id
AND fcp.concurrent_program_id = fcr.concurrent_program_id AND fcp.application_id = fcr.program_application_id
AND fcr.Oracle_Session_ID Is Not Null AND fcr.actual_start_date >= to_date('07/12/2015','mm/dd/yyyy')
AND fcr.actual_start_date < to_date('07/12/2015','mm/dd/yyyy')+7
GROUP BY
to_date('12-29-2013', 'MM-DD-YYYY') + to_Char(trunc(fcr.Actual_Start_Date) - to_date('12-29-2013', 'MM-DD-YYYY') -
to_char(fcr.Actual_Start_Date + 1, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''') + 1),
to_date('12-29-2013', 'MM-DD-YYYY') + to_Char(trunc(fcr.Actual_Start_Date) - to_date('12-29-2013', 'MM-DD-YYYY') -
to_char(fcr.Actual_Start_Date + 1, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''') + 7),
fcp.concurrent_program_name, NVL(ro.ou_name, 'system'), NULL
ORDER BY 1,4,5
I would say it's a problem with the SQL, but when I run that exact same SQL through Toad it executes, as well as outputs to Excel, with no trouble.
Any thoughts would be much appreciated.
EDIT: The call to RunQuery looks like this:
oo.RunQuery(f, Epath, Replace(CRQ, ":Dt", d), False, "HAWCSP", UName.Text, PWord.Text)
Where CRQis a variable that's hardcoded to
Public CRQ As String = "SELECT to_date('12-29-2013', 'MM-DD-YYYY') + to_Char(trunc(fcr.Actual_Start_Date) - to_date('12-29-2013', 'MM-DD-YYYY') - " & _
Chr(10) & "to_char(fcr.Actual_Start_Date + 1, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''') + 1) SWeek, " & _
Chr(10) & "to_date('12-29-2013', 'MM-DD-YYYY') + to_Char(trunc(fcr.Actual_Start_Date) - to_date('12-29-2013', 'MM-DD-YYYY') - " & _
Chr(10) & "to_char(fcr.Actual_Start_Date + 1, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''') + 7) EWeek, " & _
Chr(10) & "fcp.concurrent_program_name PName, NVL(ro.ou_name, 'system') OU, NULL BGrp, " & _
Chr(10) & "count(*) total_cr_executions,sum(round(((fcr.actual_completion_date-fcr.actual_start_date)*1440),2)) TRtM, " & _
Chr(10) & "((sum(round(((fcr.actual_completion_date-fcr.actual_start_date)*1440),2)))/count(*)) ARtM, " & _
Chr(10) & "sum(round(((fcr.actual_start_date-fcr.requested_start_date)*1440),2)) TWtM, " & _
Chr(10) & "((sum(round(((fcr.actual_start_date-fcr.requested_start_date)*1440),2)))/count(*)) AWtM " & _
Chr(10) & "FROM hotsosp.XXFND_RESPONSIBILITY_OU ro,HOTSOSP.HWC_FND_CONCUR_PROGRAMS fcp,HOTSOSP.HWC_FND_CONCUR_REQUESTS fcr " & _
Chr(10) & "WHERE ro.responsibility_id (+) = fcr.responsibility_id AND ro.application_id (+) = fcr.responsibility_application_id " & _
Chr(10) & "AND fcp.concurrent_program_id = fcr.concurrent_program_id AND fcp.application_id = fcr.program_application_id " & _
Chr(10) & "AND fcr.Oracle_Session_ID Is Not Null AND fcr.actual_start_date >= to_date(:Dt,'mm/dd/yyyy') " & _
Chr(10) & "AND fcr.actual_start_date < to_date(:Dt,'mm/dd/yyyy')+7 " & _
Chr(10) & "GROUP BY " & _
Chr(10) & "to_date('12-29-2013', 'MM-DD-YYYY') + to_Char(trunc(fcr.Actual_Start_Date) - to_date('12-29-2013', 'MM-DD-YYYY') - " & _
Chr(10) & "to_char(fcr.Actual_Start_Date + 1, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''') + 1), " & _
Chr(10) & "to_date('12-29-2013', 'MM-DD-YYYY') + to_Char(trunc(fcr.Actual_Start_Date) - to_date('12-29-2013', 'MM-DD-YYYY') - " & _
Chr(10) & "to_char(fcr.Actual_Start_Date + 1, 'DAY', 'NLS_DATE_LANGUAGE=''numeric date language''') + 7), " & _
Chr(10) & "fcp.concurrent_program_name, NVL(ro.ou_name, 'system'), NULL " & _
Chr(10) & "ORDER BY 1,4,5"
d is calculated as follows
Dim da As Date = DateAdd(DateInterval.Day, -1 * DatePart(DateInterval.Weekday, Today) - 6, Today)
Dim d As String = "'" & DatePart(DateInterval.Month, da) & "\" & DatePart(DateInterval.Day, da) & "\" & DatePart(DateInterval.Year, da) & "'"
Which comes out to '07/12/2015' for today's date (7/24/15)
