Re: How to export from Oracle to text using Perl

From: Yong Huang <yong321_at_yahoo.com>
Date: 12 Jun 2003 05:50:14 -0700
Message-ID: <b3cb12d6.0306120450.3f153302@posting.google.com>

yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0306112030.17e75dce_at_posting.google.com>...
> Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.1950edd1b793a2309897c6_at_news.la.sbcglobal.net>...
> >
> > Yes, Perl is a nice - though cryptic - language. How many developers
> > have wished that SQL included regular expressions?
>
> Speaking of regular expressions, the OWA_PATTERN package offers some
> functionality. But the usage is awkward:
>
> SQL> select * from regexptest;
>
> STR
> --------------------
> text numbers 123!
>
> SQL> declare b boolean;
> 2 begin
> 3 for i in (select str from regexptest) loop
> 4 b := owa_pattern.match(i.str, '.* \d\d\d[!.,]');
> 5 if (b = true) then
> 6 dbms_output.put_line(i.str);
> 7 end if;
> 8 end loop;
> 9 end;
> 10 /
> text numbers 123!
>
> PL/SQL procedure successfully completed.
>
> The above regexp says any characters followed by space, 3 digits and
> any of "!", "," or ".".

A few more words about the usage. Ideally, we would like to call owa_pattern.match this way: select * from regexptest where owa_pattern.match(str, '.* \d\d\d[!,.]') or select * from regexptest where owa_pattern.match(str, '.* \d\d\d[!,.]') = true. But you'll get ORA-920 (invalid relational operator), the same error you get when you select * from dual where true. So a function wrapper is needed:

SQL> create function patternmatchwrp (exp varchar2, regexp varchar2) return number as
  2 ret number;
  3 b boolean;
  4 begin
  5 b := owa_pattern.match(exp, regexp);   6 if (b = true) then
  7 return 1;
  8 else
  9 return 0;
 10 end if;
 11 end;
 12 /

Function created.

SQL> select * from regexptest
  2 where patternmatchwrp(str, '.* \d\d\d[!,.]') = 1;

STR

---

text numbers 123!

Yong Huang Received on Thu Jun 12 2003 - 07:50:14 CDT