1 PACKAGE BODY Ibc_Content_Search_Pvt AS
2 /* $Header: ibcvsrcb.pls 120.0 2005/09/01 21:40:35 srrangar noship $ */
3 TYPE WeakCurType IS REF CURSOR;
4 --
5 -- Private Utility function
6 -- remove ( and ) from p_string by replacing them with space characters
7 --
8 FUNCTION Remove_Parenthesis
9 ( p_string IN VARCHAR2 )
10 RETURN VARCHAR2
11 IS
12 l_string VARCHAR2(32000) := p_string;
13 BEGIN
14 l_string := REPLACE(l_string, '(', ' ');
15 l_string := REPLACE(l_string, ')', ' ');
16 l_string := REPLACE(l_string, '[', ' ');
17 l_string := REPLACE(l_string, ']', ' ');
18 RETURN l_string;
19 END Remove_Parenthesis;
20
21 --
22 -- Private Utility function
23 -- remove } and { from p_string by replacing them with space characters
24 --
25 FUNCTION Remove_Braces
26 ( p_string IN VARCHAR2 )
27 RETURN VARCHAR2
28 IS
29 l_string VARCHAR2(32000) := p_string;
30 BEGIN
31 l_string := REPLACE(l_string, '}', ' ');
32 l_string := REPLACE(l_string, '{', ' ');
33 RETURN l_string;
34 END Remove_Braces;
35
36 --
37 -- Private Utility function
38 -- replace white-space characters
39 --
40 FUNCTION Replace_Whitespace
41 ( p_string IN VARCHAR2,
42 p_search_option IN NUMBER )
43 RETURN VARCHAR2
44 IS
45 lenb INTEGER;
46 len INTEGER;
47 l_criteria_word VARCHAR2(2000);
48 q_word VARCHAR2(32000);
49 l_string VARCHAR2(32000) := p_string;
50 first_word BOOLEAN := TRUE;
51 l_operator VARCHAR2(4);
52
53 BEGIN
54
55 -- First convert multi-byte space character to single byte space
56 -- so that later on, when we a parsing for the space character, it
57 -- will be found
58 lenb := LENGTHB(l_string);
59 len := LENGTH(l_string);
60 IF(lenb<>len) THEN
61 l_string := REPLACE(l_string, TO_MULTI_BYTE(' '), ' ');
62 END IF;
63 lenb := LENGTHB(l_string);
64 len := LENGTH(l_string);
65 -- Pad the criteria string with blanks so that
66 -- the parse algorithm will not miss the last word
67 l_string := RPAD(l_string, lenb+1);
68 l_string := LTRIM(l_string,' ');
69
70 -- Initialize some variables
71 first_word := TRUE;
72 len := INSTR(l_string, ' '); -- position of first space character
73
74 -- Loop through the criteria string, parse to get a single criteria word
75 -- token at a time. Between each word, insert the proper Oracle Text
76 -- operator (e.g. AND, OR, ACCUM, etc.) depending on the search method
77 -- chosen.
78 WHILE (len > 0) LOOP
79 l_criteria_word :=
80 SUBSTR(l_string, 1, len-1); --from beg till char before space
81
82 IF (first_word = TRUE)
83 THEN
84 IF (p_search_option = Ibc_Content_Search_Pvt.FUZZY) --FUZZY
85 THEN
86 q_word := '?'''||l_criteria_word||'''';
87 ELSE
88 q_word := ''''||l_criteria_word||'''';
89 END IF;
90 ELSE
91 IF (p_search_option = Ibc_Content_Search_Pvt.MATCH_ALL)
92 THEN
93 l_operator := ' & ';
94 ELSIF (p_search_option = Ibc_Content_Search_Pvt.MATCH_ANY)
95 THEN
96 l_operator := ' | ';
97 ELSIF (p_search_option = Ibc_Content_Search_Pvt.FUZZY)
98 THEN
99 l_operator := ' , ?';
100 ELSIF (p_search_option = Ibc_Content_Search_Pvt.MATCH_ACCUM)
101 THEN
102 l_operator := ' , ';
103 ELSIF (p_search_option = Ibc_Content_Search_Pvt.MATCH_PHRASE)
104 THEN
105 l_operator := ' ';
106 ELSE -- if other cases
107 l_operator := ' , ';
108 END IF;
109 q_word := q_word||l_operator||''''||l_criteria_word||'''';
110 END IF;
111
112 first_word := FALSE;
113
114 -- Get the rest of the criteria string and trim off beginning whitespace
115 -- This will now be the beginning of the next criteria token
116 l_string := SUBSTR(l_string,len);
117 l_string := LTRIM(l_string, ' ');
118 -- Find the position of the next space. This will now be the end of the
119 -- next criteria token
120 len:= INSTR(l_string, ' '); -- find the position of the next space
121 END LOOP;
122 RETURN q_word;
123 END Replace_Whitespace;
124
125
126 --
127 -- Private Utility function
128 -- Handle special characters for Text query
129 --
130 FUNCTION Escape_Special_Char( p_string IN VARCHAR2 )
131 RETURN VARCHAR2
132 IS
133 l_string VARCHAR2(32000) := p_string;
134 BEGIN
135 -- Remove Grouping and Escaping characters
136 l_string := Remove_Parenthesis(l_string);
137 l_string := Remove_Braces(l_string);
138
139 -- replace all the other special reserved characters
140 l_string := REPLACE(l_string, Fnd_Global.LOCAL_CHR(39),
141 Fnd_Global.LOCAL_CHR(39)||Fnd_Global.LOCAL_CHR(39)); -- quote ' to ''
142 l_string := REPLACE(l_string, '\', '\\'); -- back slash (escape char)
143 l_string := REPLACE(l_string, ',', '\,'); -- accumulate
144 l_string := REPLACE(l_string, '&', '\&'); -- and
145 l_string := REPLACE(l_string, '=', '\='); -- equivalance
146 l_string := REPLACE(l_string, '?', '\?'); -- fussy
147 l_string := REPLACE(l_string, '-', '\-'); -- minus
148 l_string := REPLACE(l_string, ';', '\;'); -- near
149 l_string := REPLACE(l_string, '~', '\~'); -- not
150 l_string := REPLACE(l_string, '|', '\|'); -- or
151 l_string := REPLACE(l_string, '$', '\$'); -- stem
152 l_string := REPLACE(l_string, '!', '\!'); -- soundex
153 l_string := REPLACE(l_string, '>', '\>'); -- threshold
154 l_string := REPLACE(l_string, '*', '\*'); -- weight
155 l_string := REPLACE(l_string, '_', '\_'); -- single char wildcard
156
157 --bug 3209009
158 -- to make sure we will not miss '% test and %%'
159 l_string := ' '||l_string||' ';
160 l_string := REPLACE(l_string, ' % ', ' \% ');
161 l_string := REPLACE(l_string, ' %% ', ' \%\% ');
162 l_string := trim(l_string);
163
164 RETURN l_string;
165 END Escape_Special_Char;
166 --
167 -- Private Utility function
168 -- Add the next term to the query string according to search option
169 -- Parameters:
170 -- p_string VARCHAR2: the running keyword string
171 -- p_term VARCHAR2: the term to append
172 -- p_search_option NUMBER: search option, as defined in IBC_CONTENT_SEARCH_PVT
173 -- Returns:
174 -- Query string with the term appended using the appropriate search operator
175 -- Since 12.0
176 --
177 FUNCTION Append_Query_Term
178 ( p_string IN VARCHAR2,
179 p_term IN VARCHAR2,
180 p_search_option IN NUMBER )
181 RETURN VARCHAR2
182 IS
183 l_string VARCHAR2(32000) := p_string;
184 l_operator VARCHAR2(4);
185 BEGIN
186 IF( trim(p_term) IS NULL )
187 THEN
188 RETURN p_string;
189 END IF;
190
191 IF( trim(l_string) IS NULL ) -- first term
192 THEN
193 IF (p_search_option = Ibc_Content_Search_Pvt.FUZZY)
194 THEN
195 l_string := '?'''|| p_term ||'''';
196 ELSE
197 l_string := p_term;
198 END IF;
199 ELSE -- subsequent terms
200 IF (p_search_option = Ibc_Content_Search_Pvt.MATCH_ALL)
201 THEN
202 l_operator := ' & ';
203 ELSIF (p_search_option = Ibc_Content_Search_Pvt.MATCH_ANY)
204 THEN
205 l_operator := ' | ';
206 ELSIF (p_search_option = Ibc_Content_Search_Pvt.FUZZY)
207 THEN
208 l_operator := ' , ?';
209 ELSIF (p_search_option = Ibc_Content_Search_Pvt.MATCH_ACCUM)
210 THEN
211 l_operator := ' , ';
212 ELSIF (p_search_option = Ibc_Content_Search_Pvt.MATCH_PHRASE)
213 THEN
214 l_operator := ' ';
215 ELSE -- if other cases
216 l_operator := ' , ';
217 END IF;
218
219 l_string := l_string || l_operator|| p_term ;
220 END IF;
221
222 RETURN l_string;
223 END Append_Query_Term;
224
225 --
226 -- Private Utility function
227 -- This method parses the keywords based on the search syntax rule.
228 -- We support the syntax of exact phrase in the keywords (" ").
229 --
230 -- Parameters:
231 -- p_string VARCHAR2: keywords to be processed
232 -- p_search_option NUMBER: Must be one of the search option
233 -- defined in CS_K NOWLEDGE_PUB.
234 -- Returns:
235 -- The processed keyword query
236 -- Since 12.0
237 --
238 FUNCTION Parse_Keywords
239 ( p_string IN VARCHAR2,
240 p_search_option IN NUMBER )
241 RETURN VARCHAR2
242 IS
243 l_left_quote INTEGER := 0; -- position of left quote
244 l_right_quote INTEGER := 0; -- position of right quote
245 l_qnum INTEGER := 0; -- number of double quotes found so far
246 l_phrase VARCHAR2(32000); -- extracted phrase
247 l_unquoted VARCHAR2(32000) := ''; -- all unquoted text
248 l_len INTEGER;
249 TYPE String_List IS TABLE OF VARCHAR2(32000) INDEX BY PLS_INTEGER;
250 l_phrase_list String_List; -- list of extracted phrases
251 l_counter INTEGER;
252 l_processed_keyword VARCHAR(32000) := ''; --final processed keyword string
253 BEGIN
254
255 l_left_quote := INSTR(p_string, '"', 1, l_qnum + 1);
256
257 IF(l_left_quote = 0) -- no quotes
258 THEN
259 l_unquoted := p_string;
260 END IF;
261
262 WHILE (l_left_quote > 0) LOOP
263 --add unquoted portion to the unquoted string (exclude ")
264 --assert: left quote (current) > right quote (prev)
265 l_len := l_left_quote - l_right_quote - 1;
266 l_unquoted := l_unquoted || ' ' ||
267 SUBSTR(p_string, l_right_quote + 1, l_len);
268
269 --is there a close quote?
270 l_right_quote := INSTR(p_string,'"', 1, l_qnum + 2);
271 IF(l_right_quote > 0) -- add the quote
272 THEN
273 l_len := l_right_quote - l_left_quote - 1;
274 l_phrase := SUBSTR(p_string, l_left_quote + 1, l_len);
275 IF( trim (l_phrase) IS NOT NULL)
276 THEN
277 --add the quote to the list
278 l_phrase_list(l_left_quote) := l_phrase;
279 --dbms_output.put_line('phrase:' || '[' || l_phrase || ']');
280 END IF;
281 ELSE -- add the remaining text (last quote was an open quote)
282 l_unquoted := l_unquoted || ' ' || SUBSTR(p_string, l_left_quote + 1);
283 END IF;
284
285 -- now process the next phrase, try to find the open quote
286 l_qnum := l_qnum + 2;
287 l_left_quote := INSTR(p_string, '"', 1, l_qnum + 1);
288 END LOOP;
289
290 -- add the remaining text (last quote was close quote)
291 IF(l_right_quote > 0)
292 THEN
293 l_unquoted := l_unquoted || ' ' || SUBSTR(p_string, l_right_quote + 1);
294 END IF;
295
296 --add unquoted text first to final keyword string
297 IF(LENGTH( trim (l_unquoted) ) > 0)
298 THEN
299 l_processed_keyword := l_unquoted;
300 l_processed_keyword := Escape_Special_Char(l_processed_keyword);
301 l_processed_keyword :=
302 Replace_Whitespace(l_processed_keyword, p_search_option);
303 END IF;
304
305 -- loop and add all the phrases
306 l_counter := l_phrase_list.FIRST;
307 WHILE l_counter IS NOT NULL
308 LOOP
309 --dbms_output.put_line('Phrase[' || l_counter || '] = ' || l_phrase_list(l_counter));
310 --process each phrase as an exact phrase
311 l_phrase := Escape_Special_Char( l_phrase_list(l_counter) );
312 l_phrase := Replace_Whitespace(l_phrase, Ibc_Content_Search_Pvt.MATCH_PHRASE);
313 l_phrase := '(' || l_phrase || ')';
314 l_processed_keyword :=
315 Append_Query_Term(l_processed_keyword, l_phrase, p_search_option);
316 l_counter := l_phrase_list.NEXT(l_counter);
317
318 END LOOP;
319
320 -- Note some calling procedures do not properly handle an empty query
321 -- For now, simply return ' ', which will match nothing
322 IF( trim (l_processed_keyword) IS NULL)
323 THEN
324 l_processed_keyword := ' '' '' ';
325 END IF;
326
327 RETURN l_processed_keyword;
328 END Parse_Keywords;
329
330 --
331 -- Private Utility function
332 -- This function build the theme query component of a search
333 -- This is essentially wrapping the keywords with a 'about()'
334 -- intermedia function call.
335 -- The string parameter passed into the intermedia 'about()'
336 -- function has a limit of 255 characters. This function gets
337 -- around that limit by breaking the query string up into < 255
338 -- character chunks, wrapping each chunk with a separate 'about()'
339 -- function and accumulating the theme search chunks together.
340 FUNCTION Build_Intermedia_Theme_Query( p_raw_query_keywords IN VARCHAR2 )
341 RETURN VARCHAR2
342 IS
343 l_theme_querystring VARCHAR2(30000);
344 l_chunksize INTEGER := 245;
345 l_pos_raw INTEGER;
346 l_pos_endchunk INTEGER;
347 l_len_raw INTEGER;
348 l_chunk_count INTEGER := 0;
349 BEGIN
350 l_len_raw := LENGTH(p_raw_query_keywords);
351 l_pos_raw := 1;
352
353 WHILE( l_pos_raw < l_len_raw ) LOOP
354 l_chunk_count := l_chunk_count + 1;
355
356 -- Set end position of next chunck
357 IF( l_pos_raw + l_chunksize - 1 > l_len_raw ) THEN
358 l_pos_endchunk := l_len_raw;
359 ELSE
360 l_pos_endchunk := l_pos_raw + l_chunksize - 1;
361 -- adjust the endchunk to the last word boundary
362 l_pos_endchunk := INSTR( p_raw_query_keywords, ' ',
363 -(l_len_raw-l_pos_endchunk+1) );
364 END IF;
365
366 -- wrap next chunk with 'about()' and append to
367 -- the theme query string buffer with accumulate.
368 IF( l_chunk_count > 1 ) THEN
369 l_theme_querystring := l_theme_querystring || ',';
370 END IF;
371
372 l_theme_querystring := l_theme_querystring || 'about(' ||
373 SUBSTR(p_raw_query_keywords,
374 l_pos_raw,
375 l_pos_endchunk - l_pos_raw + 1)||')';
376
377 l_pos_raw := l_pos_endchunk + 1;
378 END LOOP;
379 RETURN l_theme_querystring;
380 END Build_Intermedia_Theme_Query;
381 --
382 -- Private Utility function
383 -- This is the main query-rewrite function. Given a raw
384 -- user-entered keyword string and the search method chosen,
385 -- this function will construct the appropriate Oracle Text
386 -- query string. This is independent of whether the search
387 -- is for solutions or statements or anything else.
388 -- NOTE: This function does NOT incorporate product, platform,
389 -- category, or other metadata information into the Text query.
390 -- Those predicates are left to the caller to append.
391 FUNCTION Build_Intermedia_Query
392 ( p_string IN VARCHAR2,
393 p_search_option IN NUMBER )
394 RETURN VARCHAR2
395 IS
396 l_about_query VARCHAR2(32000) := p_string;
397 l_keyword_query VARCHAR2(32000) := p_string;
398 l_iQuery_str VARCHAR2(32000); -- final intermedia query string
399 lenb INTEGER;
400 len INTEGER;
401 BEGIN
402
403 -- If the Search option chosen is THEME Search or if there is
404 -- no search option chosen, then rewrite the raw text query
405 -- with the theme search query and concatenate it with a regular
406 -- non-theme based rewritten query
407 IF (p_search_option = Ibc_Content_Search_Pvt.THEME_BASED OR
408 p_search_option IS NULL) --DEFAULT
409 THEN
410 l_keyword_query :=
411 Build_Keyword_Query
412 ( p_string => l_keyword_query,
413 p_search_option=> NULL);
414 l_about_query :=
415 Build_Intermedia_Theme_Query( Escape_Special_Char(l_about_query) );
416 l_iQuery_str := '('||l_about_query||' OR '||l_keyword_query||')';
417 ELSE
418 -- Else just build the standard, non-theme based rewritten query
419 l_keyword_query :=
420 Build_Keyword_Query
421 ( p_string => l_keyword_query,
422 p_search_option => p_search_option );
423
424 l_iQuery_str := '( ' || l_keyword_query || ' )';
425 END IF;
426
427 -- Return the rewritten text query criteria
428 RETURN l_iQuery_str;
429
430 END Build_Intermedia_Query;
431 --
432 -- Private Utility function
433 -- Convert Text query critiera string into keyword query
434 -- with special characters handled
435 -- Since 12.0, delegates to Parse_Keywords
436 --
437 FUNCTION Build_Keyword_Query(
438 p_string IN VARCHAR2,
439 p_search_option IN NUMBER
440 ) RETURN VARCHAR2
441 IS
442 --l_string varchar2(32000) := p_string;
443 BEGIN
444 --l_string := Escape_Special_Char(l_string);
445 --return Replace_Whitespace(l_string, p_search_option);
446 RETURN parse_keywords(p_string, p_search_option);
447 END Build_Keyword_Query;
448
449 -- Constructs the intermedia query that should be used in the
450 -- CONTAINS predicate for a Content search
451 --
452 FUNCTION Build_Simple_Text_Query
453 (
454 p_qry_string IN VARCHAR2,
455 p_search_option IN NUMBER
456 )
457 RETURN VARCHAR2
458 IS
459 l_query_str VARCHAR2(30000) := p_qry_string;
460
461 BEGIN
462
463 IF (p_search_option = Ibc_Content_Search_Pvt.INTERMEDIA_SYNTAX) -- Intermedia Syntax
464 THEN
465 RETURN l_query_str;
466 END IF;
467
468 l_query_str := Build_Intermedia_Query( l_query_str, p_search_option);
469
470 RETURN l_query_str;
471 END Build_Simple_Text_Query;
472 END Ibc_Content_Search_Pvt;
[Home] [Help]