Oracle’s answer to OBIEE’s case sensitivity
Fmap Image Not Shown When Printing to PDF only – Fmap Function Showing White Space Instead – Image Can be Seeing in Dashboard
October 24, 2010Few Oracle’s support treasures
December 12, 2010Someone asked Oracle’s support the following question: “Why some of the prompt case sensitive, some of them are case insensitive”
You noticed that some prompts are case sensitive and some are not. User selects a multi-select prompt, with the “BEGIN WITH” matching functionality, ie, User enters “We” (without quotes) and presses the Go button, the user receives “No Choices Available.” message. User then enters “We%” the users receives results.
On other dashboards the steps describes above return results by entering “We” OR “we” into the “BEGIN WITH” matching functionality.
By default, Oracle database is case sensitive, this can be easily verified by run a query to test it, for example:
SELEECT PROD_NAME FROM PRODUCTS WHERE PROD_NAME LIKE ‘p%’
ORDER BY PRODNAME;
This query should not return any result as the first character for product name stored in database is upper case.
However, when using BI Answers, you may find some of the prompt with condition IS EQUAL TO/LIKE is case insensitive. This happens when
1. CASE_SENSITIVE_CHARACTER_COMPARISON = OFF is set (BIEE default setting) for Oracle Database in NQConfig.ini file.
2. The table is set cachable in BIEE Physical model.
So the data is cached in BI server is case insensitive, consequently the prompts retriving data from cacheable table is Case Insesitive, and the prompts retriveing from Non-cacheable table is Case Sesitive.
You also can turn off the cache in BIEE Administrator for the table in question, this can be done by double click on the table in Physical Model, click on General tab, uncheck Cacheable option for the table in question, save it. Restart BI Server. You then will find it is case sensitive, but for those tables set cacheable, they are still case insensitive.
It is recommended to set CASE SENSITIVE_CHARACTER_COMPARISON according to database searching behavior, so SENSITIVE_CHARACTER_COMPARISON=ON is set for Oracle Database in NQConfig.ini file to enforce consistent Case Sensitive searching behavior.
REF: [ID 862731.1]
