Usage of Oracle binding variables with LIKE in C#

Question

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?

Duplicate of this question: stackoverflow.com/questions/1412023/… — CodingGorilla
– CodingGorilla, Commented Sep 24, 2010 at 19:48
sql = "SELECT somedata FROM sometable WHERE Upper(machine) LIKE '%' || upper(P_machine) || '%'); //where P_machine is oracle parameter this is working for me. — Santosh Kumar Bind
– Santosh Kumar Bind, Commented Jul 25, 2024 at 10:09

OMG Ponies · Accepted Answer · 2010-09-24 19:48:08Z

36

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.

answered Sep 24, 2010 at 19:48

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

PJ. Over a year ago

You, sir, are correct. That was indeed the last option, the one I missed. Thank you!

Dave Cooper Over a year ago

Thank 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 Over a year ago

@DaveCooper the || concatenates the '%', it is a command for concatenation.

Dave Cooper Over a year ago

@EdgarRochaCarvalho thank you! Didn't know this - it also explains why it was failing all the times I tried using the + operator!

Dan Over a year ago

This helped me get around the identical issue in our project. Thx!

Dmitri · Accepted Answer · 2021-04-23 16:37:51Z

Here is a full query example:

string commandText = "SELECT LastName, FirstName FROM PEOPLE WHERE UPPER(LastName) LIKE '%' || :lastName || '%' AND UPPER(FirstName) LIKE '%' || :firstName || '%'";

string oradb = "yourDatabaseConnectionStringHere"; // Might want to add Using statement for this code and try catch

OracleConnection conn = new OracleConnection(oradb); // C#
conn.Open();
OracleCommand cmd = new OracleCommand
{
     Connection = conn,
     CommandText = commandText,
     CommandType = CommandType.Text
};

/*IMPORTANT: adding parameters must be in order how they are in order in the SQL statement*/
cmd.Parameters.Add(new OracleParameter("lastName", model.LastName.Trim().ToUpper()));
cmd.Parameters.Add(new OracleParameter("firstName", model.FirstName.Trim().ToUpper()));

OracleDataReader dr = cmd.ExecuteReader();

Collectives™ on Stack Overflow

Usage of Oracle binding variables with LIKE in C#

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related