search cancel

Advanced search not working


Article ID: 7081


Updated On:


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


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.


Service Catalog 14.1


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".


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) 



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.


1558703290194000007081_sktwi1f5rjvs16q7u.png get_app
1558703287912000007081_sktwi1f5rjvs16q7t.png get_app