Advanced search not working

Products

• CA Service Catalog
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

When an user tries to make any search using Advanced Search, some results are not being retrieved.

Ex.: Log into Service Catalog and navigate to "Home >> Requests", then type to search for some words which exists in the offerings. Some will return values, others will not.

In this example, "desktop" and "notebook" not returned any results, while "telefone" (which translates for "phone") return results.

 

<Please see attached file for image>

 

<Please see attached file for image>

 

If you change browser from local language (Portuguese) to English you get the same behavior. 

Installing testfix as suggested in technical document TEC1277094 not change the behavior.

Changing the variables as per technical document TEC1767563 also does not help.

Cause

You can face this issue when using Oracle database. By setting Catalog view in trace mode, the following similar error will appear at the logs:

 

ERROR [http-bio-8080-exec-18] [CService] XXXXXX 

java.sql.SQLException: ORA-06502: PL/SQL: erro: character string buffer too small numérico ou de valor 

ORA-06512: em "MDBADMIN.USM_FN_REMOVE_HTML", line 12 

 

This issue is related to a value defined in Oracle function "USM_FN_REMOVE_HTML" which is fixed to 500. The same issue not happens in MS-SQL database as this value is defined as "max".

Environment

Service Catalog 14.1

Resolution

You can change the value in Oracle function up to 32767. There is no MAX in Oracle, instead of max, the maximum value can be used is 32767 in Oracle. The similar to SQL Server max is 32767 in Oracle database.

 

=============== 

change the usm_fn_remove_html function in oracle 

from 500 to 32767 

 

create or replace 

FUNCTION usm_fn_remove_html (htmlintext IN VARCHAR2) 

RETURN VARCHAR2 

IS 

htext VARCHAR2(32767); -- ~ equivalent to max in sql 

st INT; 

en INT; 

len INT; 

htmltext VARCHAR2(32767); -- ~ equivalent to max in sql 

htmlctag INT; 

htmlstag INT; 

 

Additional Information

First check if technical documents mentioned helps on resolving this issue (TEC1277094 and TEC1767563). If that is not the case and Catalog MDB database is running at Oracle database, consider using the solution provided at this document.