OWA Pattern Match
- From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Sep 2004 07:09:21 -0700
One of our developers is trying to use the following regular =
expression.
^[a-z]+(\-[a-z]+)*\/\d{7,9}$
If we test it with a parser outside of Oracle it performs as expected.
Regular expression: ^[a-z]+(\-[a-z]+)*\/\d{7,9}$
Input text: hep-ex/0408086
Match!
Input text: qwerty\876654 No Match!
But inside of Oracle OWA_PATTERN.MATCH complains the expression is =
illegal in Oracle 9, and crashes in Oracle 8. Here's an anonymous block =
function, and the results from 9i. This function should return null as =
there is a match, but if not it should suffer a divide by 0 error. =20
SQL> set echo on
SQL> -- FUNCTION MATCH RETURNS BOOLEAN
SQL> -- Argument Name Type In/Out =
Default?
SQL> -- ------------------------------ ----------------------- ------ =
--------
SQL> -- LINE VARCHAR2 IN
SQL> -- PAT VARCHAR2 IN
SQL> -- FLAGS VARCHAR2 IN =
DEFAULT
SQL> --
SQL> -- this one assumes that the parameters are Line followed by=20
SQL> Pattern declare
2 preprintregex VARCHAR2(30) :=3D '^[a-z]+(\-[a-z]+)*\/\d{7,9}$';
3 i integer :=3D 0;
4 begin
5 IF Sys.Owa_Pattern.Match('hep-ex/0408086', preprintregex) THEN
6 null;
7 else
8 i :=3D 1/i;
9 end if;
10 end;
11 .
SQL> /
declare
*
ERROR at line 1:
ORA-20001: In omatch: illegal pattern found
ORA-06512: at "SYS.OWA_PATTERN", line 766
ORA-06512: at "SYS.OWA_PATTERN", line 869
ORA-06512: at "SYS.OWA_PATTERN", line 966
ORA-06512: at "SYS.OWA_PATTERN", line 989
ORA-06512: at "SYS.OWA_PATTERN", line 998
ORA-06512: at line 5
So what's wrong with the pattern?.
Here's the breakdonw of the regular expression
-- start of line ^
-- followed by 1 or more letters [a-z]+
-- optionally followed by... (
-- a dash (-) and \-
-- one or more letters [a-z]+
-- repeat this group as necessary )*
-- followed by a slash (/) \/
-- followed by 7 to 9 digits \d{7,9}
-- end of line $
--
//www.freelists.org/webpage/oracle-l
Other related posts:
