RE: OWA Pattern Match

Thanks, but, pray tell, "Does it work?"

Ian=20

-----Original Message-----
From: Gogala, Mladen [mailto:Mladen.Gogala_at_aetn.com]=20 Sent: Friday, September 17, 2004 7:26 AM To: MacGregor, Ian A.; oracle-l_at_freelists.org Subject: RE: OWA Pattern Match

Try REGEXP_LIKE instead of OWA_PATTERN. Jonathan Gennick wrote a little = booklet which explains how to use regular expressions with Oracle. In = your case, it's a plug in replacement for OWA_PATTERN.MATCH. It would go = like
this:

declare

   preprintregex VARCHAR2(30) :=3D '^[a-z]+(\-[a-z]+)*\/\d{7,9}$';    i integer :=3D3D 0;
begin

   IF REGEXP_LIKE('hep-ex/0408086', preprintregex) THEN

      null;
   else

      i :=3D 1/i;
   end if;
end;
/

Here is an excerpt from the manual:
REGEXP_LIKE REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE = performs regular expression matching instead of the simple pattern = matching performed by LIKE. This condition evaluates strings using = characters as defined by the input character set.

This condition complies with the POSIX regular expression standard and = the Unicode Regular Expression Guidelines. For more information, please = refer to Appendix C, " Oracle Regular Expression Support".

regexp_like_condition::=3D
Description of regexp_like_condition.gif follows Description of the = illustration regexp_like_condition.gif

    *

      source_string is a character expression that serves as the search = value. It is commonly a character column and can be of any of the = datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

It's described in detail in Jonathan's book and 10g manuals.

--
Mladen Gogala
A & E TV Network
Ext. 1216




> -----Original Message-----

> From: MacGregor, Ian A. [mailto:ian_at_slac.stanford.edu]

> Sent: Friday, September 17, 2004 10:09 AM

> To: oracle-l_at_freelists.org

> Subject: OWA Pattern Match

>=20
>=20


> One of our developers is trying to use  the following regular =3D=20

> expression.

>=20


> ^[a-z]+(\-[a-z]+)*\/\d{7,9}$

>=20


> If we test it with a parser outside of Oracle it performs  as=20

> expected.

>=20


> Regular expression: ^[a-z]+(\-[a-z]+)*\/\d{7,9}$

> Input text:         hep-ex/0408086

> Match!

>=20


> Input text: qwerty\876654 No Match!

>=20


> But inside of Oracle OWA_PATTERN.MATCH complains the expression is =3D =




> illegal in Oracle 9, and crashes in Oracle 8.

> Here's an anonymous block =3D function, and the results from 9i.  This =




> function should return null as =3D there is a match, but if not it=20

> should suffer a divide by 0

> error.   =3D20

>=20


> SQL> set echo on

> SQL> -- FUNCTION MATCH RETURNS BOOLEAN

> SQL> --  Argument Name                  Type                 =20

>   In/Out =3D

> Default?

> SQL> --  ------------------------------

> ----------------------- ------ =3D

> --------

> SQL> --  LINE                           VARCHAR2                IN

> SQL> --  PAT                            VARCHAR2                IN

> SQL> --  FLAGS                          VARCHAR2             =20

>   IN     =3D

> DEFAULT

> SQL> --

> SQL> -- this one assumes that the parameters are Line followed by=3D20 =




> SQL> Pattern  declare

>   2    preprintregex   VARCHAR2(30) :=3D3D=20

> '^[a-z]+(\-[a-z]+)*\/\d{7,9}$';

>   3    i integer :=3D3D 0;

>   4  begin

>   5   IF Sys.Owa_Pattern.Match('hep-ex/0408086', preprintregex) THEN

>   6    null;

>   7   else

>   8    i :=3D3D 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

>=20


> So what's wrong with the pattern?.

>=20


> Here's the breakdonw of the regular expression

>=20


> -- 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                        $

>=20


> --

> http://www.freelists.org/webpage/oracle-l

>=20
--
http://www.freelists.org/webpage/oracle-l