Auto-generated sql code: single backslash as escape character fails

Question

I am querying oracle 9i database with

SELECT * FROM table WHERE column LIKE '%' || 'SomeText || '%' ESCAPE '\';

and it does not work with the error "escape character must be a character string of length 1" (error ORA-01425), and then express 10g in the oracle database.

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

I cannot edit SQL because it is automatically generated through Telerik OpenAccess ORM.

Linq code that leads to SQL above:

activity.Name.Contains. ("SOMETEXT")

I would like both databases to process ESCAPE '\' ... Or, instead, have a different way of finding table elements by their name or description.

Thanks in advance!

Answer 1

Not familiar with Linq, but I'm a little confused by the fact that you are executing a query - you just paste the generated code into SQL * Plus, working with two databases, where can this be explained at least?

If you do this in SQL * Plus, do a show escape in each environment; I suspect that 9i will report escape "\" (hex 5c) , while 10g will report escape off . This may indicate that evacuation processing was previously configured in instance 9i, but not (presumably later) 10g.

If any of these aspects is still relevant, try running set escape \ in a 10g session and try the \\ version again. And in 9i, try to escape off and try a single version \ . Both should now work.

Assuming you're still with me, the next question is: why does 9i have this setting; probably a login.sql or glogin.sql file that automatically installs it. You may be able to remove this if it does not affect anything else to allow the generated code to work unchanged.

I do not think that it will matter if you are going to execute the code in another way; not sure if you just test and debug the generated code in SQL * Plus and end up executing it elsewhere (implicitly knowing Linq again), in which case this may be a temporary problem anyway.

I'm also not sure if you run away at all ...

Answer 2

Try:

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

Typically, the ESCAPE character in LIKE is used to indicate the search characters "%" and "_"

Answer 3

you could avoid the backslash problem. Try using braces around escaped characters.

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

Answer 4

Does this happen for every input, or only certain lines? The problem may not be with the request, but with the input. If there is an odd number of backslashes, Oracle might try to escape from something that doesn't need an escape.

For example, this works because it escapes "%":

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

But this fails because it tries to escape the "a", which does not require escaping:

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

Can you change the string before passing it to the function and fix the odd backslashes?

Answer 5

If someone stops with the same problem ... My problem was that I was dealing with the "NVARCHAR2" fields. I got help on this issue on oracle forums :)

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

works on both because the dummy field is varchar2. If it was nvarchar2, then the part of the request that could (only possible!) Cause problems would be "escape" \ '"(my oracle 9i wants escape' \, my oracle 10g wants' \\).

To fix the problem, instead of using the generated ORM code, I wrote a stored procedure (only when Im looking for strings), where I process the nvarchar2 fields as follows: where TableName.ColumnName as N '%' || 'SomeText || N '%' escape N '\'

And his working tone :)

However, this does not explain how they were handled differently by two oracle servers (10g express on my local PC and 9i) with the same NVARCHAR2 columns and with the same SQL queries - this remains a question. Therefore, for those who encounter similar problems, it may be useful to find out if the problem is with nvarchar2 (I did not know that this could be a problem) and try to work around this.