Menu on the form does not list any values and results in "No entries in menu" when Oracle database is set to Case Insensitive

Version 3
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    APPLIES TO:

    Remedy AR System Server, Remedy IT Service Management, Oracle 18c



    PROBLEM:

     

    Site menu on CTM:People form (also on Asset forms, Product Categories and Resolution Product Categories etc.) did not return any list even though Sites does exist in the system.

    The SQL associated with the population of the field is shown below.
    SELECT DISTINCT T538.C260000001 FROM T538 WHERE ((((T538.C1000000001 = ' ') OR (' ' = ' ')) AND ((T538.C200000012 = ' ') OR (' ' = ' ')) AND ((T538.C200000007 = ' ') OR (' ' = ' ')) AND (T538.C1000000073 = 0) AND (T538.C1000000081 = 1) AND (T538.C7 = 1))) AND ( ROWNUM <= 20101 ) ORDER BY T538.C260000001 ASC

    Oracle version: 18c i.e. 12.2.0.2
    AR and ITSM 19.02

     


    CAUSE:

    Oracle, Bug 27416997 and Case Insensitive queries failing due to NULL is NOT NULL predicate


    SOLUTION:

     

    The "Db-Case-Insensitive" parameter was set to "T".

    It was found that if Remedy was being run in Case Insensitive mode, certain use cases would not return expected rows. In this example, "Site" drop down menu on the CTM:People form had returned 72 rows (as expected) when Remedy was in Case Sensitive mode, however NO RECORDS were listed when it was ran in Case Insensitive mode.

    It turned out to be hitting a known Oracle Bug 27416997 (check Metalink Doc ID 2390584.1).You will need Oracle credentials to access this Bug, you may reach out to your DBA). The Bug has been resolved in Oracle 19.1.

    To address this Site menu issue, we set below parameters (as per above Oracle solution doc) and it resolved the issue:
           alter system set "_optimizer_generate_transitive_pred"=FALSE scope=both

    BMC Communities Blog reference:
    https://communities.bmc.com/community/bmcdn/bmc_remedy_ar_system/blog/2019/09/23/oracle-bug-27416997-and-case-insensitive-queries-failing-due-to-null-is-not-null-predicate

     

     


    Article Number:

    000173457


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles