0

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

enter image description here

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
enter image description here

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

Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265

1 Answers1

2

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]*$
Community
  • 1
  • 1
DavidG
  • 113,891
  • 12
  • 217
  • 223
  • You are a bloody genius! It works with `string.Format`. Thank you. – Matas Vaitkevicius Nov 27 '14 at 09:47
  • Haha well thanks! Just be careful to validate the string as it seems you are taking it from a publicly visible action method which means people can easily perform SQL injection. – DavidG Nov 27 '14 at 09:48
  • Option 2. If someone needs here's regex to clean input `new Regex(@"[0-9][\,0-9]*").Match(id).Value;`. David it would be nice if you could copy regex in under option two, as anyone who chooses it will have to do this step. – Matas Vaitkevicius Nov 27 '14 at 10:25
  • So you don't want to cope with spaces in the input? For example `1, 2, 3, 4`? – DavidG Nov 27 '14 at 10:28
  • Also, personally I would just use RegEx to validate rather than clean. I'll add that to my answer. – DavidG Nov 27 '14 at 10:29
  • Not really - normally it's going to be `string.Join(',',idData);` but you are more than welcome to add this enhancement. I agree with more strict validation only idea. – Matas Vaitkevicius Nov 27 '14 at 10:31