How do I create a case sensitive query in Oracle forms?

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 als开发者_如何学Goo 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.

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

my_field like '12%'

Uses the index. The subset is then filtered with

upper(my_field) = '12345A'

So it might not be as bad as you think....

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).