Oracle.ManagedDataAccess.Client.OracleException - ORA-01722: invalid number

Question

I am working in .NET 4.5 C#, oracle 11g environment.

I am experiencing peculiar behaviour, when I call following SQL statement from code by using datacontext

I get ORA-01722: invalid number

StackTrace:

   at Corp.DataServices.ExecuteQueryHandler.HandleQueryException(Exception exception) in c:\dev\CCTech Main\Corp\Corp.Conveyancing.DataServices\FluentData.cs:line 4022
   at Corp.DataServices.ExecuteQueryHandler.ExecuteQuery(Boolean useReader, Action action) in c:\dev\CCTech Main\Corp\Corp.Conveyancing.DataServices\FluentData.cs:line 3993
   at Corp.DataServices.DbCommand.QueryMany[TEntity,TList](Action`2 customMapper) in c:\dev\CCTech Main\Corp\Corp.Conveyancing.DataServices\FluentData.cs:line 3857
   at Corp.DataServices.DbCommand.QueryMany[TEntity](Action`2 customMapper) in c:\dev\CCTech Main\Corp\Corp.Conveyancing.DataServices\FluentData.cs:line 3882
   at Corp.Dashboard.Controllers.HomeController.GetInstructionsJson(String id) in c:\dev\CCTech Main\Corp\Corp.Conveyancing.Dashboard\Controllers\HomeController.cs:line 65
   at lambda_method(Closure , ControllerBase , Object[] )
   at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.ActionInvocation.InvokeSynchronousActionMethod()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
   at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()
   at System.Web.Mvc.Async.AsyncResultWrapper.End[TResult](IAsyncResult asyncResult, Object tag)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass48.<InvokeActionMethodFilterAsynchronouslyRecursive>b__41()

Now to the peculiar part.
When I execute same exact sql directly from PL/SQL it works just fine

What is going on? There aren't any casts in the statement. I must be missing something obvious here.

Answer 1

You can't pass in a parameter in this way. When you do that, it will render the SQL as something like this:

WHERE productid IN ('1,2,3,4,5')

Which will attempt to cast the value as a single string to a number. Instead you can either:

• Pass the values as an array using a table valued parameter. I've not done this before but pretty sure it's possible, for example How to use Array/Table Parameter to Oracle (ODP.NET 10g) via ADO.NET/C#?)
• Forget using parameters and use string concatenation. Not usually a great idea (SQL injection etc.) but simple to do. To validate the input with RegEx (and assuming you don't have any spaces between the numbers) you could use this to match the input (slightly modified from comment by @LIUFA): ^[0-9][\,0-9]*$