autogenerated sql code: single backslash as an escape character is failing.

Question

Hello everyone,

I'm querying an oracle 9i database with:

SELECT * FROM table WHERE column LIKE '%' || ‘someText’ || '%' ESCAPE '\';

and it fails with the error "escape character must be character string of length 1" ( ORA-01425 error), while succeeding in an oracle express 10g database.

Making it a double backslash (ESCAPE '\\') solves the problem for the oracle 9i database, but generates instead the same ORA-01425 error for the 10g database.

I cannot edit the SQL since it's auto-generated via Telerik OpenAccess ORM.

The Linq code that leads to the SQL above is:

activity.Name.Contains.("someText")

I would like both databases to handle the ESCAPE '\'... Or instead, have another way of searching table items by their name or description.

Thanks in advance!

Answer 1

Try:

  SELECT * FROM TABLENAME 
  WHERE COLUMNNAME LIKE '\%' ESCAPE '\';

Generally ESCAPE symbol in LIKE used for allow search symbols '%' and '_'

Answer 2

you could avoid the backslash issue altogether. Try using the curly braces around the escaped characters instead.

http://download.oracle.com/docs/cd/B10500_01/text.920/a96518/cqspcl.htm

Answer 3

Not familiar with Linq but I'm a bit confused about where you're executing the query - are you just pasting the generated code into SQL*Plus running against two databases, where that behaviour can at least be explained?

If you are doing it in SQL*Plus, do a show escape in each environment; I suspect 9i will report escape "\" (hex 5c) while the 10g will report escape off. This might indicate that escape handling has previously been set up in the 9i instance but not in the (presumably more recent) 10g one.

If any of this has turned out to be relevant so far, try doing set escape \ in the 10g session and try the \\ version again. And in 9i try doing escape off and try the single-\ version there. Both should now work.

Assuming you're still with me, the next question is why 9i has that setting; there's probably a login.sql or glogin.sql file that's setting it automatically. You might be able to get that removed, as long as it won't affect anything else, to allow the generated code to run unaltered.

I don't think any of that will be relevant if you're going to be executing the code some other way; not sure if you're just testing and debugging the generated code in SQL*Plus and will eventually execute it elsewhere (lack of knowledge of Linq again), in which case this may be a transitory problem anyway.

I'm also not sure what you're actually escaping anyway...

Answer 4

Does it fail for every input or just specific strings? The problem may not be with the query, but with the input. If there is an odd number of backslashes, Oracle may try to escape something that shouldn't need an escape.

For example, this works because it's escaping the '%':

select * from dual  where 'test' like '%'||'\'||'%' escape '\';

But this fails because it's trying to escape 'a', which doesn't need escaping:

select * from dual  where 'test' like '%'||'\a'||'%' escape '\';

Can you modify the string before it's passed to the function and fix odd backslashes?

Answer 5

In case anyone stops by with the same problem... My issue was that I was dealing with “NVARCHAR2” fields. I received help with this issue in the oracle forums :)

This query: select * from dual where 'dummy' like '%' escape '\';

works on both because the field ‘dummy’ is varchar2. If it were nvarchar2, the part of the query that could (only possibly!) cause problems would be the “escape '\'” part (my oracle 9i wants escape ‘\’, my oracle 10g wants ‘\\’).

To overcome the problem, instead of using the ORM’s autogenerated code, I have written a stored procedure (only when I’m searching for strings), where I handle nvarchar2 fields like this: where TableName.ColumnName like N'%' || ‘someText’ || N'%' escape N'\'

And it’s working fine :)

That doesn’t explain, however, how having the same NVARCHAR2 columns, and the same SQL queries, they were handled differently by the two oracle servers (the 10g express on my local PC and the 9i) – that remains a question. So for anyone running into similar problems, it may be good to know if it’s a nvarchar2 issue (I had no idea it could be a problem), and try working around it.