5 Replies Latest reply on Sep 3, 2013 9:06 PM by Ady Keeling

    Case insensitivity in LOVs




          We are in the process of modifying a largeish 10g application in order to achieve case insensitivity in search mode and we've run into a performance issue with LOVs that I'm hoping someone could shed some light on. For text items validation works automatically just by setting CASE_INSENSITIVE_QUERY to true. A partial solution to the LOV-case is to set "Validate From List" to Yes, user upper()-statements and let the LOV be validated against the SQL in the record group. The problem is that the optimizer doesn't appear to use any indexes when upper() is used and it leads to performance issues with large result sets.


      So the gist of the problem is "How can case insensitivity in LOV validation be achieved in a performant manner"?


      Thanks in advance,


        • 1. Re: Case insensitivity in LOVs
          Ady Keeling

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


          1 person found this helpful
          • 2. Re: Case insensitivity in LOVs

            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


            1. 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")
            2. 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,

              - Nik

            • 3. Re: Case insensitivity in LOVs
              Christian Erlinger

              Maybe this helps:

              Ask Tom "Making Oracle Case insensitive "


              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.



              • 4. Re: Case insensitivity in LOVs

                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,


                • 5. Re: Case insensitivity in LOVs
                  Ady Keeling

                  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.