1 person found this helpful
The simple solution is to create a function based index on upper applied to the relevant column, e.g.:
create index dept_upper_ix on departments (upper(department_name));
Thanks, sounds logical, we'll give it a spin.
Another (more complex) problem we stumbled onto is that the SQL of the record group attached to the LOV has two roles
- It's used in the "validate from list"-context for input validation, in which case there can't be any upper-handling ("TeSt" can't be accepted if the DB provides the values "TEST" and "test")
- It's providing the values for the LOV, in which case upper-handling should be used
Let's again assume the DB values ["TEST", "test"]
KEY-LISTVAL and F9 works fine but when "validate from list" is used, Forms handles the validation and opens the LOV automatically if required. So if "TE" is entered and focus is moved
away from the field, the internal validation process ("validate from list" without upper-handling) grabs the value "TEST" even if it should open the LOV and provide the options "TEST" and
"test". On the other hand, if you enter "Te" and exit the field, a warning is given that no such value exists.
One option we are considering is removing the "validate from list" feature from the field and move all validation to WHEN-VALIDATE-ITEM-triggers, where the upper-handling is performed and
check if there are multiple hits and opening the LOV if required.
As you might imagine, doing this for all LOVs in hundreds of forms is time-consuming so if anyone has any insights from similar requirements, we'd love the hear about them.
Thanks in advance,
Maybe this helps:
assuming you are on db version >= 10g.
you might have a problem when your LOV query returns TEST and test and you type TE as in any case it should return all matches (ignoring the case of course) so you'd get TEST and test.
Thanks for your suggestion, it appears to solve many of our problems! (BTW, where did the posting disappear?)
Another minor issue that comes to mind is that we usually use LOVs so that the initial value is set in the field before starting the LOV and for this we have in the record group where-clause something like "b.product like :header.product ||'%'" where header.product points to the field in question. Now we get '%' in the find field instead of the pre-populated value (and get all the results).
So the question could be summarized as "is there a way to copy the field value into the find-field of the LOV when it's opened?"
Hope I got the question right, I'm forwarding it for a fellow developer ;-)
Thanks in advance,
HI glad my post helped, I was trying it out locally and it worked, but then doing it without any upper handling, with an ordinary lov seemed to work too, so I removed the post. I didn't have time to investigate further and post again though. I am on forms 11g though... I haven't got the post handy, so if you want to repost it please feel free.