Issue with parameterizing ROWNUM using OracleCommand (C#) using managed ODP.NET

Question

I am trying to parameterise ROWNUM when trying to query an Oracle database using the latest ODP.NET managed driver.

SQL Query is "...WHERE ROWNUM <= :ROWSTOLOCK"

When I try to add ROWNUM as a parameter as shown below:

dbCommand.Parameters.Add("ROWSTOLOCK", Oracle.ManagedDataAccess.Client.OracleDbType.Int64, 25 , ParameterDirection.Input);

and I do try to execute the query

dbCommand.ExecuteNonQuery() 

I get an exception:

"Additional information: ORA-01008: not all variables bound"

Row num is a number as we can see in a previous Stack Overflow post: What is the OracleType of ROWNUM

However I cannot find Oracle.ManagedDataAccess.Client.OracleDbType.Number. I tried with all other numeric types availabe in Oracle.ManagedDataAccess.Client.OracleDbType enum.

I find it hard to think that this a limitation of managed driver (number type unavailable).

Code Snippet:

        string sql = "...WHERE  ROWNUM & lt;= :ROWSTOLOCK";
        string connectionString = "my connection string";
        Oracle.ManagedDataAccess.Client.OracleConnection connectiont = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString);

        Oracle.ManagedDataAccess.Client.OracleCommand dbCommand = new Oracle.ManagedDataAccess.Client.OracleCommand(sql, connectiont);
        dbCommand.Parameters.Add("ROWSTOLOCK", Oracle.ManagedDataAccess.Client.OracleDbType.Decimal, 25, ParameterDirection.Input);
        connectiont.Open();
        int rowsAffected = dbCommand.ExecuteNonQuery();

Complete exception:

ORA-01008: not all variables bound
Oracle Data Provider for .NET, Managed Driver
       at OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
       at 
OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean bFirstIterationDone)
       at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, Boolean isFromEF)
       at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
       at QuickTest1.Program.Main(String[] args) in C:\Users\george\Documents\Visual Studio 2015\Projects\Program.cs:line 44
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()

Any help is genuinely appreciated.

Answer 1

The snippet cannot be compiled. You use dbCommand reference and just line after you declare it. Also you add parameter and then instantiate a new instance of OracleCommand. So the command doesn't have any parameter. And then I expect it to fail on ORA-01745: invalid host/bind variable name because ROWNUM is reserved word.