IBM Support

How to match non-alphanumeric characters on Oracle when using ClearQuest multiline text field in query filters

Technote (troubleshooting)


Problem(Abstract)

IBM Rational ClearQuest can not match non-alphanumeric characters when queries use filters on multiline text fields.

Symptom

ClearQuest result will get no results even when there are non-alphanumeric characters in multiline text fields.


Cause

Non-alphanumeric characters will be ignored by Oracle when creating indexes or using filters to match text in a multiline text field.

By default, multiline text fields cannot be used in query filters in IBM Rational ClearQuest on an Oracle database. To use these fields, an index must be created for each of these fields. The method is to use the "setup_cq_ora_user_clob.sql" script, as explained in this article:

http://www.ibm.com/developerworks/rational/library/07/0531_clearquest/

When using non-alphanumeric characters in the query filter string, the result will not include the non-alphanumeric characters, because these characters are not normally part of the index. Here is an example of how that happens. A multiline text field in a record has value:

####

When the query filter is "####", the result will be empty, because the "#" character will not be used when the index is created. In another words, "####" will not be part of the index.


Resolving the problem

To solve this problem, non-alphanumeric characters can be added to the index using the Oracle <printjoins> attribute.

Working in ClearQuest versions of 8.0.0.9 or 8.0.1.2, there is another step to enable non-alphanumeric characters to be indexed when using "setup_cq_ora_user_clob.sql". This allows matching those non-alphanumeric characters. Such as:

,&=?{}\()[]-;~|$!>*%_@`#^</:.


Do this by creating a copy of the "setup_cq_clob_index.sql" script, and modify the copy with a text file editor, such as Notepad. Edit the following line to contain only the non-alphanumeric characters that need to match. Then use the modified script to create the desired indexes. The default shown in the following example is to use all the non-alphanumeric characters that Oracle allows.

holdastring := 'BEGIN ctx_ddl.set_attribute(''cq_lexer_casecontrol'', ''printjoins'','',&=?{}\()[]-;~|$!>*%_@`#^</:.'' ); END;';

Beware that the matching behavior may be unexpected the intent is to match only some of these non-alphanumeric characters, but all the non-alphanumeric characters are in the <printjoins> attribute.

After the <printjoins> attribute is enabled, if the non-alphanumeric characters that are expected to be matched are any of the characters in this list

,&=?{}\()[]-;~|$!>*_

then the ClearQuest behavior flag "CLOB_CASE_AND_INDEX" needs to be set to a value of 3. These characters have special meanings and are reserved characters on Oracle. Setting the behavior flag tells ClearQuest to escape these characters so they match as normal characters instead of having special meaning for Oracle. The same characters can also be manually escaped with a backslash. For example:

"\,", "\&", "\=", ..., "\_"

Setting the behavior flag will escape all these characters, so they can never be used for their special meaning for Oracle.

The percent character, "%", is also a reserved character for Oracle and cannot be excluded with the <printjoins> attribute. This character has to always be escaped manually in order to use it as a literal, even if the behavior flag is set. If the query filter string has "%", then other non-alphanumeric characters must be manually escaped, because ClearQuest will not escape them automatically.

Document information

More support for: Rational ClearQuest
Database Configuration/Connectivity - Oracle

Software version: 8.0.0.9, 8.0.1.2

Operating system(s): Linux, Windows

Reference #: 1654457

Modified date: 17 December 2013