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.

Without seeing the whole query and all the parameter create statements, this is hard to answer. One common pitfall when using ODP.NET that leads to this ORA error is not knowing that ODP.NET defaults to "Bind by position". The order of the parameters you create is what matters, not the name. You can switch to bind by name by setting the BindByName property. — Christian Shay, Oct 30 '15 at 05:16

score 1 · Answer 1 · answered Oct 27 '15 at 18:01

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.

answered Oct 27 '15 at 18:01

Husqvik

5,669
1
19
29

Thanks a lot for pointing out. I just updated the snippet to be compile-able. – George Philip Oct 27 '15 at 18:05
After the changes the code works for me, no matter if I choose Decimal or Int... types. – Husqvik Oct 27 '15 at 18:09
That is really interesting. The exception occurs when you try to execute the query. Did you try to execute it? – George Philip Oct 27 '15 at 18:40
You didn't post the exact query, I just tried `SELECT 1 FROM DUAL WHERE ROWNUM <= :P` – Husqvik Oct 27 '15 at 18:41
That worries me now. So it works on your machine? It just became harder to troubleshoot. Are you using the Oracle.ManagedDataAccess Client – George Philip Oct 27 '15 at 20:37
I tested both managed and non-managed. I use the newest ODAC, 12.1.0.2.4, released Oct 5, 2015. – Husqvik Oct 27 '15 at 20:52
Thanks a lot for helping me out here, but I am still facing the issue and not sure what is the next best way troubleshoot this. I will try with a simpler query but cant think of why it should be the query. – George Philip Oct 27 '15 at 21:14
Are you running the SQL script directly in TOAD or through .NET? – Ryan Ternier Oct 28 '15 at 14:08
I ran mine though .NET using LinqPad – Husqvik Oct 28 '15 at 14:23

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

1 Answers1