Re[2]: OWA Pattern Match

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Fri, 17 Sep 2004 11:37:40 -0400
Message-ID: <15168104291.20040917113740@gennick.com>

It looks like Ian is using 9i, so the new, regex functionality won't be available to him (it's 10g stuff). I've been told that the OWA_PATTERN package has its problems (my coauthor once sent me a long list...) Ian, it's likely you've hit something that OWA_PATTERN just does not handle.

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.

Friday, September 17, 2004, 10:26:07 AM, Gogala, Mladen (Mladen.Gogala_at_aetn.com) wrote:

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

GM> declare
GM>    preprintregex   VARCHAR2(30) := '^[a-z]+(\-[a-z]+)*\/\d{7,9}$';
GM>    i integer :=3D 0;
GM> begin
GM>    IF REGEXP_LIKE('hep-ex/0408086', preprintregex) THEN
GM>       null;
GM>    else
GM>       i := 1/i;
GM>    end if;

GM> end;
GM> /

GM> Here is an excerpt from the manual:
GM> REGEXP_LIKE

GM> REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs
GM> regular expression matching instead of the simple pattern matching performed
GM> by LIKE. This condition evaluates strings using characters as defined by the
GM> input character set.

GM> This condition complies with the POSIX regular expression standard and the

GM> Unicode Regular Expression Guidelines. For more information, please refer to GM> Appendix C, " Oracle Regular Expression Support".

GM> regexp_like_condition::=
GM> Description of regexp_like_condition.gif follows
GM> Description of the illustration regexp_like_condition.gif

GM>     *

GM>       source_string is a character expression that serves as the search

GM> value. It is commonly a character column and can be of any of the datatypes GM> CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

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

GM> --
GM> Mladen Gogala
GM> A & E TV Network
GM> 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
>>
>>
>> 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
>>

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

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

Received on Fri Sep 17 2004 - 10:33:23 CDT