5

I'm getting what I can only describe as the oracle.ManagedDataAccess package from nuget seeming to add characters onto the end of my query.

My ultimate goal is to supply bind variables to the below SQL in the form of command parameters from C#.

My query: (works fine if executed with SQL Developer, returns 3 strings and a datetime)

with max_shift as (
  select max(shi.shift_id) shift_id
  from source_schema.site_instance ins
    join source_schema.tu_move_shifts shi on ins.instance_id = shi.instance_id
  where ins.instance_name = 'SiteOneSubsiteTwo' 
),
max_values as (
  select max(end_time) event_time 
  from max_shift ms
    join source_schema.events_extract ev on ev.move_shift_id = ms.shift_id
  where ev.site_code = 'SiteOne'

  union all 

  select max(destination_arrive_time) event_time
  from max_shift ms 
    join source_schema.movements_extract mov on mov.move_shift_id = ms.shift_id
  where mov.destination_site_code = 'SiteOne'
)
select 'Data Type One' as Type,
  'SiteOne' as Site,
  'Staging' as DataStore,
  min(event_time)
from max_values ;

The C# running it:

using ( var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(GetConnectionString(theconnectionstring.ToString())))
{
    using (var command = connection.CreateCommand())
    {
        connection.Open();
        var sourceQuery = connection.CreateCommand();
        sourceQuery.CommandTimeout = 0;
        sourceQuery.BindByName = true;
        //sourceQuery.CommandType = CommandType.StoredProcedure;
        sourceQuery.CommandType = CommandType.Text;
        sourceQuery.CommandText = GetSourceQuery(thequery);

        using (var reader = sourceQuery.ExecuteReader())
        {
            //stuff
        }
    }
}

But on the line "using (var reader = sourceQuery.ExecuteReader())" (shown below as line xxx), it crashes with the following:

Oracle.ManagedDataAccess.Client.OracleException (0x000003A5): ORA-00933: SQL command not properly ended
   at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   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, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()
   at MonitoringService.MonitoringService.<ExecuteQueryAsync>d__10.MoveNext() in C:\_dev\Script_Consolidation\Monitoring\Monitoring\TSTLatencyMonitoringService.cs:line xxx

If this is submitted as a CommandType.StoredProcedure, I get expectable stored proc related errors when executing on the server but get instead the error "ORA-06550 PLS-00103: Encountered the symbol "," when expecting one of the following:" ... making me think that Oracle.ManagedDataAccess module is adding something to the command.

user544141
  • 90
  • 1
  • 6
  • 1
    Could we see how you build your string `thequery` from that query? – Keyur PATEL Feb 01 '17 at 09:08
  • Perhaps field "Type" requires escaping like `select Data Type One` as "Type" ? Perhaps it is a reserved word? (Although you say you run it on SQL Developer with no problems...). Other than that I would try creating the simplest scenario just to test if the dll has no problems in handling the `With` clause. – Veverke Feb 01 '17 at 09:14
  • 1
    Try remove ";" at the end of your query – PinBack Feb 01 '17 at 09:26
  • I know some older data provides do not support sub-query factoring (i.e. `WITH ... AS (...)`. However, Oracle.ManagedDataAccess is fairly new, so I assume that is not the reason. – Wernfried Domscheit Feb 01 '17 at 13:51
  • 2
    Tried all these suggestions, but the winner ended up being the obvious one: @PinBack I owe you a beer. Thanks! When I was using CommandType.StoredProcedure (as recommended by nearly every SO and oracle documentation I could find...) removing the semicolon just changed the error to "expecting ';'" instead of another character... However using CommandType.Text seems to like Bind Variables AND expects a lack of query terminator. All works now! Thanks kindly everyone. – user544141 Feb 02 '17 at 02:17
  • Second question @PinBack how do I mark your comment as the answer? :-D – user544141 Feb 02 '17 at 02:18
  • You can post your own answer and accept it since @PinBack does not seem to want to post an answer. – phoog Apr 09 '19 at 18:57

1 Answers1

11

If you have a select statement you must remove ";" at the end of the query. For an SQL statement with a "begin end" Block you have remove the "/" at the end (here you need the ";")

PinBack
  • 2,499
  • 12
  • 16