1 package body owa_match
2
3 as
4
5 --- Special characters list, note 1 space in the beginning and
6 --- a single quote escaped at the end.
7 SPECIAL_CHARS constant varchar2(27) := ' @*()+-/=\<>:"|&?{}[]'''
8 || chr(9) -- tab
9 || chr(10) -- new line
10 || chr(12) -- form feed
11 || chr(13); -- cr
12
13 TRANS_CHARS constant varchar2(27) := '***************************';
14
15 function match_pattern
16 (
17 p_string in varchar2,
18 p_simple_pattern in owa_util.vc_arr default empty_vc_arr,
19 p_complex_pattern in owa_util.vc_arr default empty_vc_arr,
20 p_use_special_chars in boolean default true
21 )
22 return boolean is
23 l_string varchar2(512) := upper(p_string);
24 l_count integer := p_simple_pattern.count;
25 begin
26
27 if (p_use_special_chars) then
28 --- First, check the string for any special characters,
29 --- use translate function to replace any special character
30 --- in the list with a '*'.
31 --- Look for any '*' in the string, this means a special character
32 --- has been found and the check failed.
33 if (instr(translate(l_string, SPECIAL_CHARS, TRANS_CHARS),'*') > 0) then
34 return true;
35 end if;
36 end if;
37
38 --- Second, check the incoming string against default
39 --- exclusions list
40 for i in 1..l_count loop
41 if (l_string like upper(p_simple_pattern(i)) escape '\') then
42 return true;
43 end if;
44 end loop;
45
46 --- Finally, check if we have any dynamic exclusions passed to us.
47 --- If so, check the string against those exclusions as well.
48 --- This is very slow, so should be used as the last resort, when
49 --- cannot be covered by other checks.
50 l_count := p_complex_pattern.count;
51 if (l_count > 0) then
52 for i in 1..l_count loop
53 --- Use owa_pattern to do case-insensitive search
54 if (owa_pattern.match(
55 line => l_string,
56 pat => upper(p_complex_pattern(i)),
57 flags => 'i'))
58 then
59 return true;
60 end if;
61 end loop;
62 end if;
63
64 return false;
65
66 end match_pattern;
67
68 end owa_match;