"ORA-00933: SQL command not properly ended" using Oracle.ManagedDataAccess

Question

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.

Answer 1

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 ";")