如何在 Oracle 表单中创建区分大小写的查询？
我有一个基于表的块。如果我在输入查询模式下输入“12345”，它会创建一个查询，
WHERE my_field = '12345'
如果我输入“12345A”，它会
WHERE (upper(my_field) = '12345A' AND my_field like '12%')
很糟糕，因为 my_field 被正常索引（不在 upper(my_field) 上）。我尝试在混合和大写之间切换“大小写限制”属性，以及在是和否之间切换“不区分大小写的查询”，似乎没有任何帮助。我还有一个块级 PRE-QUERY 触发器（触发器以 RETURN; 语句开始）设置为覆盖，因此不应干扰查询的形成，但它仍然会混乱。
关于我还可以尝试什么的任何想法？
编辑：
WHEN_NEW_FORM_INSTANCE 触发器内对某些附加库进行了一个模糊的函数调用，该函数将所有触发器块的项目重置为 CASE_SENSITIVE_QUERY = TRUE。永远不会猜到。
I have a block based on a table. If I enter "12345" in enter query mode, it creates a query with
WHERE my_field = '12345'
If I enter "12345A", it goes
WHERE (upper(my_field) = '12345A' AND my_field like '12%')
which is bad, because my_field is indexed normally (not on upper(my_field)). I have tried toggling "Case restriction" attribute between mixed and upper, and "Case insensitive query" between yes and no, nothing seems to help. I also have a block level PRE-QUERY trigger (trigger starts with a RETURN; statement) set on override, so nothing should mess with the formation of the query, yet it still messes up.
Any ideas on what else I could try?
EDIT:
There was an obscure function call within WHEN_NEW_FORM_INSTANCE trigger to some attached library that reset all trigger block's items to CASE_SENSITIVE_QUERY = TRUE. Never would have guessed.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不确定查询如何更改为该形式；
WHERE (upper(my_field) = '12345A' AND my_field like '12%'
首先检查表单中是否没有输入查询或预查询触发器。有人可能在更高级别附加了触发器。Oracle 没有那么聪明地重写查询。检查您是否绑定到表而不是视图或存储过程，...
如果所有其他方法都失败，请在数据黑色中启用查询触发器并自己重写 where 子句。这非常简单。
在发布之前提供 Oracle 表单的版本。
Not sure how the query is getting changed to that form;
WHERE (upper(my_field) = '12345A' AND my_field like '12%'
First check that there are no enter query or prequery triggers in the form. Somebody might have attached a trigger at a higher level. Oracle is not that smart to rewrite the query.Check that you are tying to a table not a view or stored procedure,...
If all else fails, enable the query triggers in the data black and rewrite the where clause yourself. It is pretty straightforward.
Give version of oracle forms before you post.
使用
索引。然后对子集进行过滤，
因此它可能没有您想象的那么糟糕......
The
Uses the index. The subset is then filtered with
So it might not be as bad as you think....
最天真的问题，你能更新该列，使其全部大写吗？我的意思是这会给您的应用程序带来一些不便吗？
如果可以的话，可以使用数据库触发器来处理它，以确保它始终是大写的。
如果不能，那么我建议您创建另一个字段，并使用数据库触发器将其更新为大写。
您还可以创建一个函数索引，使其位于上层（my_field）。
The most naive question, Can you update the column so it's all uppercase? I mean would it cause some inconvenience to your app?
If you can, it could be handled with a database trigger to ensure it's allways uppercase.
If you can't, then I suggest you create another field that you keep updated to uppercase with a database trigger.
You can also create a function index so it's upper(my_field).