OWA Pattern Match

From: MacGregor, Ian A. <ian_at_slac.stanford.edu>
Date: Fri, 17 Sep 2004 07:09:21 -0700
Message-ID: <7F24308CD176594B8F14969D10C02C6C0B4D46@exch-mail2.win.slac.stanford.edu>

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 $

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 17 2004 - 09:06:45 CDT