RE: OWA Pattern Match
- From: "Gogala, Mladen" <Mladen.Gogala@xxxxxxxx>
- To: "'ian@xxxxxxxxxxxxxxxxx'" <ian@xxxxxxxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 17 Sep 2004 10:26:07 -0400
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) := '^[a-z]+(\-[a-z]+)*\/\d{7,9}$';
i integer :=3D 0;
begin
IF REGEXP_LIKE('hep-ex/0408086', preprintregex) THEN
null;
else
i := 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::=
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@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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
