Accented character search results being excluded (Oracle)

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

When using SDM with Oracle, one may find search results being affected due to the presence of accented characters in the last name, where searching through listings by last name will have entries which may be exlcuded despite no data partitioning or search filter warranting their exclusion.

Maldonado
Márquez
Medina
Mejía
Méndez
Mendoza
Moreno
Muñoz
Murray

The highlighted names may be excluded in a search as a result of the accented characters in the text

Environment

Release 17.x and higher (Oracle implementation)
CA Service Desk Manager

Cause

Issue is specific to Oracle.  When performing such text searches, the DB SELECT statements will be performed with the given sessions set by executing:

alter session set NLS_COMP=LINGUISTIC
alter session set NLS_SORT=BINARY_CI

There are two settings in the NX.env which control Oracle searches and sorting:

@NX_ORACLE_CASE_INSENSITIVE=1
@NX_ORCL_SORTING=XXXX

XXXX is the desired language locale configuration, which will vary depending on localisation settings for Oracle.

Resolution

In the example of Spanish, one may try setting the above in NX.env by running:

pdm_options_mgr -c -s ORCL_SORTING -v SPANISH_CI -a pdm_option.inst
pdm_options_mgr -c -s ORCL_SORTING -v SPANISH_CI -a pdm_option.inst -t

Case sensitivity can also be controlled by running:

pdm_options_mgr -c -s ORACLE_CASE_INSENSITIVE -v 1 -a pdm_option.inst
pdm_options_mgr -c -s ORACLE_CASE_INSENSITIVE -v 1 -a pdm_option.inst -t

Additional Information

Information on using pdm_options_mgr to introduce and adjust NX variable settings is available in KB Article 18247