Usage of Oracle binding variables with LIKE in C#
Try:
sql = "SELECT somedata FROM sometable WHERE machine LIKE :machineName || '%' ";
Because of the BIND variable, there wouldn't need to be single quotes around it. But the % is not, so I would expect it needing to be encapsulated.
Related videos on Youtube
PJ.
Developer-turned Oracle DBA at an international manufacturing company in charge of designing, developing and supporting Manufacturing-related applications ranging from ASP .NET webcharts to database interfaces and machine-automation applications.
Updated on May 04, 2022Comments
-
PJ. about 2 years
As part of an effort to stop using dynamic SQL generation and encourage use of bind variables, I am running into some problems.
I am querying an Oracle 9i database from an ASP.NET page using Oracle Data Providers for .NET
The query is
sql = "SELECT somedata FROM sometable WHERE machine = :machineName ";
I define the Oracle Parameter as follows
OracleParameter parameter = new OracleParameter(); parameter.ParameterName = "machineName"; parameter.OracleDbType = OracleDbType.Varchar2; parameter.Value = machine; //machine is a variable of type string parameterList.Add(parameter);
This works fine for "=" operator. But I just can't seem to get it to work with "LIKE". I don't know how to format the query so that it accepts usage of the "%" wildcard.
I have tried:
sql = "SELECT somedata FROM sometable WHERE machine LIKE :machineName% "; sql = "SELECT somedata FROM sometable WHERE machine LIKE ':machineName%' "; sql = "SELECT somedata FROM sometable WHERE machine LIKE :machineName||% ";
and also:
parameter.Value = machine+'%';
but all I get are ORA-00911 (illegal character) and ORA-01036 (illegal name/value) exceptions.
What am I doing wrong?
-
PJ. almost 14 yearsYou, sir, are correct. That was indeed the last option, the one I missed. Thank you!
-
Dave Cooper almost 9 yearsThank you so much for this answer! I'm working on a node project with an oracle database driver and was experiencing the same issue. Just out of curiosity, how come I need the two pipes (||) before the wildcard operator (%)?
-
Edgar Carvalho almost 9 years@DaveCooper the || concatenates the '%', it is a command for concatenation.
-
Dave Cooper almost 9 years@EdgarRochaCarvalho thank you! Didn't know this - it also explains why it was failing all the times I tried using the + operator!
-
Dan over 4 yearsThis helped me get around the identical issue in our project. Thx!