Re: Searching case-insensitively

The Oracle Forms Reference manual describes how Oracle Forms makes partial use of an index for its case-insensitive query option:  

Case Insensitive Query  

Determines whether the operator can perform case-insensitive queries on the text item.  

Applies To: text item  

Set: Designer, programmatically  

Refer To Built-In:

         GET_ITEM_PROPERTY           SET_ITEM_PROPERTY   Default: False  

Restrictions: Setting this property to True can cause queries to take longer to execute.  

Usage Notes: Case-insensitive queries are optimized to take advantage of an index. For example, assume you perform the following steps:  

                Create an index on the EMP table.  

                Set the Case Insensitive Query property on ENAME to True.  

                In Enter Query mode, enter the name 'BLAKE' into :ENAME.  

                Execute the query.  

Oracle Forms constructs the following statement:  

SELECT * FROM EMP WHERE UPPER(ENAME) = 'BLAKE' AND    (ENAME LIKE 'Bl%' OR ENAME LIKE 'bL%' OR    ENAME LIKE 'BL%' OR ENAME LIKE 'bl%');  

The last part of the WHERE clause is performed first, making use of the index. Once the database finds an entry that begins with bl, it checks the UPPER(ENAME) = 'BLAKE' part of the statement, and makes the exact match.    

• Rick