Re[2]: OWA Pattern Match

Jonathan Gennick <jonathan@xxxxxxxxxxx> · Fri, 17 Sep 2004 11:37:40 -0400

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@xxxxxxxxxxx

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@xxxxxxxxxxx and 
include the word "subscribe" in either the subject or body.

Friday, September 17, 2004, 10:26:07 AM, Gogala, Mladen 
(Mladen.Gogala@xxxxxxxx) 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@xxxxxxxxxxxxxxxxx] 
>> Sent: Friday, September 17, 2004 10:09 AM
>> To: oracle-l@xxxxxxxxxxxxx
>> 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                        $
>> 
>> --
>> //www.freelists.org/webpage/oracle-l
>> 
GM> --
GM> //www.freelists.org/webpage/oracle-l

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

Other related posts: