Oracle Text tips

-->To solve the issue of searching in nested tags using oracle text, use 'inpath' rather than 'within'. With 'inpath' exact path can be specified with various options to fine tune the query to yield precise search results.

Refer to oracle documentation (http://docs.oracle.com/cd/E11882_01/text.112/e24436/cqoper.htm#CCREF0300) for more information on inpath.


-->To search for word across multiple sections in oracle text index use 'accum' instead of boolean operators. Weighting can also be used with accum in scenarios where one section needs to be emphasized more than others. 'within' and 'inpath' can be combined with accum.

eg: contains(doc, '(abc within head)*3 accum(abc inpath /head/body/p[1])') > 0

In this example, head is a section in oracle text index, abc is the search term and 3 is the weight. This query will search for the word abc in head and also in 'p' tag. However, if abc occurs in title section it gets scored more than if it appeared in the 'p' tag.

Refer to oracle documentation (http://docs.oracle.com/cd/E11882_01/text.112/e24436/cqoper.htm#CCREF0300 ) for more information on the working of accum.

-->To escape special characters while doing a wildcard search into oracle text, use \ instead of {}.

eg: water\,% will work. {water,} will not work.

-->A true wild card search is not possible in oracle text

i.e., contains(doc,'%') > 0 , '%' can be used when prefixed or suffixed with search term (can be a word or a single character).

Oracle Apex 4.2 tips

--> When passing an apex item between pages, whose value is a string with comma, escape between forward slashes. If not escaped the string will be truncated at comma.

eg: intsead of passing &PX_ABC. pass \&PX_ABC.\
This information is available in help text for the item but is easy to miss for beginners.