自动生成的 sql 代码：单个反斜杠作为转义字符失败
我正在查询 Oracle 9i 数据库：
SELECT * FROM table WHERE column LIKE '%' || '一些文本' || '％' 逃脱 '\';
它失败并出现错误“转义字符必须是长度为 1 的字符串”（ORA-01425 错误），而在 Oracle Express 10g 数据库中则成功。
将其设为双反斜杠 (ESCAPE '\\') 可解决 Oracle 9i 数据库的问题，但会为 10g 数据库生成相同的 ORA-01425 错误。
我无法编辑 SQL，因为它是通过 Telerik OpenAccess ORM 自动生成的。
导致上述 SQL 的 Linq 代码是：
Activity.Name.Contains.("someText")
我希望两个数据库都能处理 ESCAPE '\'...或者，采用另一种方法按名称搜索表项或描述。
提前致谢！
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
不熟悉 Linq，但我对执行查询的位置有点困惑 - 您是否只是将生成的代码粘贴到针对两个数据库运行的 SQL*Plus 中，至少可以解释该行为？
如果您在 SQL*Plus 中执行此操作，请在每个环境中执行
show escape；我怀疑 9i 会报告escape "\" (hex 5c)，而 10g 会报告escape off。这可能表明之前已在 9i 实例中设置了转义处理，但在（可能是更新的）10g 实例中未设置。如果到目前为止其中任何一个结果是相关的，请尝试在 10g 会话中执行
set escape \并再次尝试\\版本。在 9i 中尝试执行escape off并尝试那里的 single-\版本。现在两者都应该可以工作了。假设你仍然支持我，下一个问题是为什么 9i 有这样的设置？可能有一个 login.sql 或 glogin.sql 文件会自动设置它。您也许可以将其删除，只要它不会影响其他任何内容，以允许生成的代码不改变地运行。
如果您打算以其他方式执行代码，我认为这些都无关紧要；不确定您是否只是在 SQL*Plus 中测试和调试生成的代码，并最终在其他地方执行它（再次缺乏 Linq 知识），在这种情况下，无论如何这可能是一个暂时的问题。
我也不确定你实际上在逃避什么......
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 escapein each environment; I suspect 9i will reportescape "\" (hex 5c)while the 10g will reportescape 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 doingescape offand 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...
尝试：
通常 LIKE 中的 ESCAPE 符号用于允许搜索符号 '%' 和 '_'
Try:
Generally ESCAPE symbol in LIKE used for allow search symbols '%' and '_'
您可以完全避免反斜杠问题。尝试在转义字符周围使用花括号。
http://download.oracle.com/docs/ cd/B10500_01/text.920/a96518/cqspcl.htm
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
是每个输入都失败还是只是特定字符串失败？问题可能不在于查询，而在于输入。如果反斜杠的数量为奇数，Oracle 可能会尝试转义不需要转义的内容。
例如，这可以工作，因为它转义了 '%'：
但这会失败，因为它试图转义 'a'，而 'a' 不需要转义：
你能在字符串传递给函数之前修改字符串并修复奇怪的反斜杠吗？
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 '%':
But this fails because it's trying to escape 'a', which doesn't need escaping:
Can you modify the string before it's passed to the function and fix odd backslashes?
万一有人遇到同样的问题...我的问题是我正在处理“NVARCHAR2”字段。我在 oracle 论坛中收到了有关此问题的帮助:)
此查询： select * from Dual where 'dummy' like '%' escape '\';
对两者都有效，因为字段“dummy”是 varchar2。如果是 nvarchar2，则查询中可能（仅可能！）导致问题的部分将是“转义 '\'” 部分（我的 oracle 9i 想要转义 '\'，我的 oracle 10g 想要 '\\'）。
为了解决这个问题，我没有使用 ORM 的自动生成代码，而是编写了一个存储过程（仅当我搜索字符串时），在其中处理 nvarchar2 字段，如下所示： where TableName.ColumnName like N'%' || '一些文本' || N'%' escape N'\'
并且工作正常:) 然而
，这并不能解释具有相同 NVARCHAR2 列和相同 SQL 查询的两个 Oracle 服务器（10g Express 上的 10g Express）如何以不同方式处理它们。我本地的 PC 和 9i）——这仍然是一个问题。因此，对于遇到类似问题的任何人来说，最好知道这是否是 nvarchar2 问题（我不知道这可能是一个问题），并尝试解决它。
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.