Advanced search not working

Document ID:  TEC1554637
Last Modified Date:  06/20/2017
{{active ? 'Hide' : 'Show'}} Technical Document Details

Products

  • CA Service Catalog

Releases

  • CA Service Catalog:Release:14.1

Components

  • CA SERVICE CATALOG:USVCT
Problem:

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.

 figure1.png

 

figure2.png

 

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.

Environment:
Service Catalog 14.1
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".

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.

Please help us improve!

Will this information enable you to resolve your issue?

Please tell us what we can do better.

{{feedbackText.length ? feedbackText.length : '0'}}/255

{{status}}

Not what you were looking for?

Search Again >

Product Information

Support by Product >

Communities

Join a Community >

Chat with CA

Just give us some brief information and we'll connect you to the right CA ExpertCA sales representative.

Our hours of availability are 8AM - 5PM CST.

All Fields Required

connecting

We're matching your request.

Unfortunately, we can't connect you to an agent. If you are not automatically redirected please click here.

  • {{message.agentProfile.name}} will be helping you today.

    View Profile


  • Transfered to {{message.agentProfile.name}}

    {{message.agentProfile.name}} joined the conversation

    {{message.agentProfile.name}} left the conversation

  • Your chat with {{$storage.chatSession.messages[$index - 1].agentProfile.name}} has ended.
    Thank you for your interest in CA.


    Rate Your Chat Experience.

    {{chat.statusMsg}}

agent is typing