ORA-01036: illegal variable name/number

.NET
1

Hello guys,

I am very much new to .NET/C# and using Oracle for DB operations


// not working - with paremeter
string sql = "select m.*, u.* FROM managers m, users u ";
sql += " WHERE u.userid = m.user_id ";
sql += " WHERE COMPANY_NAME LIKE = '%:COMPANY_NAME%'";
dbm.AddParameter("COMPANY_NAME", "ABC company");

//Error is: ORA-01036: illegal variable name/number


//it works - without parameter
string sql = "select m.*, u.* FROM managers m, users u ";
sql += " WHERE u.userid = m.user_id ";
sql += " WHERE COMPANY_NAME LIKE = 'ABC company'";

Tried lots of things and wasted my time.

Please can any find tell me the reason of this error?

its throwing error when I try to bind using parameters.

Thanks in advance.

2

you’re not allowed to have more than one WHERE clause

you can have multiple conditions in the WHERE clause, but these need to be combined using AND and/or OR operators

here’s a tip: when you’re developing a web interface, test your queries outside of .net/c first

that way you’ll encounter the actual database error messages more easily

3

Sorry it was a typo.


// not working - with paremeter

string sql = "select m.*, u.* FROM managers m, users u ";

sql += " WHERE u.userid = m.user_id ";

sql += " AND COMPANY_NAME LIKE = '%:COMPANY_NAME%'";

dbm.AddParameter("COMPANY_NAME", "ABC company");

Thank you for the reply. NOw I have corrected it. Please reply soon

many thanks.

4

Note: I used pl/sql developer and it was working fine there.

but using c# and parameterized binding above SQL it’s not working…

5

Last time I worked with an oracle DB (1yr ago), one had to add a cursor as an output paramater so that selects would work. Are you doing that?

6

We used procedures… I think in your situation is not required.

7

the syntax must be like: " AND COMPANY_NAME LIKE = :COMPANY_NAME"

8

No I am using plain parametrized queries.


the syntax must be like: " AND COMPANY_NAME LIKE = :COMPANY_NAME"
// not giving accurate results. like should not be case-sensitive.
// abc - no result
// ABC = result found
9

Forgive me for making a wild guess, a COLLATION issue?

10

What do you mean by this? I have simple varchar column with no such collation.
Thank you for the reply

11

Well, I’m not familliar with the Oracle database, which is why I said “wild guess”. but with MySQL, what collation is used can effect the results. AFAIK it’s only for characters outside the first 128, but if you have characters like umlauts etc. (i,e. your ABC abc example was a simplification) then it might be something worth exploring. http://dev.mysql.com/doc/refman/5.1/en/charset-collation-effect.html
*Again, MySQL not Oracle, but I imagine Oracle has something similar.

12

LIKE = is wrong, it should be just LIKE

13

the syntax must be like: " AND COMPANY_NAME LIKE :COMPANY_NAME"
// not giving accurate results. like should not be case-sensitive.
// abc - no result
// ABC = result found

yes its like that. like
“=” << was typo. sorry again.

14

as for case sensitivite I’m with Mittineague, it could be a collation issue, server settings or ODP.Net settings.