I have a simple query string. I'm getting a weird database error when i use datetime in querystring and can't figure out why. Query works on oracle without error.
Dim selectedDate As String = (Today.AddDays(-2000)).ToString("d-MMM-yyyy", CultureInfo.CreateSpecificCulture("en-US")).ToUpper()
Dim cmd As New OracleCommand
cmd.Connection = xConnection
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT * FROM ORDER_DETAIL WHERE ORDER_TYPE='PO' AND STATUS<>'CANCEL' AND PN='XXXXXX' AND LAST_DATE_RECEIVED>TO_DATE('27-JUN-2013','DD/mm/YYYY')"
Dim dr1 As OracleDataReader = cmd.ExecuteReader()
I think it's not related Oracle server or provider because it doesn't give an error if i remove the date part. Do you have any idea?
Exception details below:
Oracle.ManagedDataAccess.Client.OracleException
HResult=0x80004005
Message=ORA-03111: Break received on communication channel
Source=Oracle Data Provider for .NET, Managed Driver
StackTrace:
at OracleInternal.TTC.TTCExecuteSql.ReceiveExecuteResponse(Accessor[]& defineAccessors, Accessor[] bindAccessors, Boolean bHasReturningParams, SQLMetaData& sqlMetaData, SqlStatementType statementType, Int64 noOfRowsFetchedLastTime, Int32 noOfRowsToFetch, Int32& noOfRowsFetched, Int64& queryId, Int32 longFetchSize, Int64 initialLOBFetchSize, Int64[] scnFromExecution, Boolean bAllInputBinds, Int32 arrayBindCount, DataUnmarshaller& dataUnmarshaller, MarshalBindParameterValueHelper& marshalBindParamsHelper, Int64[]& rowsAffectedByArrayBind, Boolean bDefineDone, Boolean& bMoreThanOneRowAffectedByDmlWithRetClause, List`1& implicitRSList, Boolean bLOBArrayFetchRequired)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()
at VB_TestApp.Module1.Main() in c:\users\.....
Inner Exception 1:
NetworkException: ORA-03111: Break received on communication channel
The below syntax needs to be changed slightly. The syntax in the question has a date format syntax error.
cmd.CommandText = "SELECT * FROM ORDER_DETAIL WHERE ORDER_TYPE='PO' AND STATUS<>'CANCEL' AND PN='XXXXXX' AND LAST_DATE_RECEIVED>TO_DATE('27-JUN-2013','DD/mm/YYYY')"
can be changed to standard syntax
AND LAST_DATE_RECEIVED> date '2013-06-27'"
Oracle Supports ANSI date format - Standard Docs Link
User contributions licensed under CC BY-SA 3.0