Re: pl/sql question and owa_pattern question

PL/SQL is the fastest thing of them all when it comes to executing SQL commands, but there are things which simply aren't practical in 9.2 PL/SQL. Regular expression processing is one of those things. Fortunately, you can mix the two. Without DBI, perl scripts simply woudn't be very useful. Of course, there are things that are faster then even the fastest perl script. Lexer written in C is one of them and you don't need much work to write one, either, but using OCI is not easy. OCI is a library written to confuse the enemy, not to help developer. Using plain and simple regex or PCRE within a C program is the same thing as above, but slightly more complicated then a lexer. For the specific task of manipulating patterns and resolving regular expressions, I use perl almost exclusively because I find it an optimal tradeoff between ease of use and performance. If performance is a paramount, as in real time application processing, then you'll have to resort to C and, possibly, write an external procedure and, thus, enabling oracle to use C regex calls or even pcre. I was toying with the idea of enabling oracle to use PCRE but I gave up when I read that 10g will have that included.

On 11/21/2003 11:59:31 AM, Guang Mei wrote:
> Perl is a good tool for text processing. But our program is already written
> in pl/sql long time ago and there are intensive db calls in this pl/sql
> program. (text processing is only part of it). So I can not change that.
>
> BTW I did a comparison study a while ago for some of our pl/sql packages
> (specifically for our application). When there are lots of db calls (select,
> insert, update and delete), pl/sql package is faster than correponding perl
> program (I made sure sqls are prepared once and used bind variables in perl.
> All code were executed on the unix server, no other programs were running,
> etc). That's why we stick to pl/sql because our app need the performance.
> Others may have different results, it all depends on what the code does.
>
> Guang
>
> -----Original Message-----
> Mladen Gogala
> Sent: Thursday, November 20, 2003 11:14 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I don't know about PL/SQL but here is how I would get separate words from a
> big string:
>
> #!/usr/bin/perl -w
> use strict;
> my (@ARR);
> while (<>) {
> chomp;
> @ARR = split(/[^0-9a-zA-Z_\.,<>]/);
> foreach (@ARR) {
> print "$_\n";
> }
> }
>
> There is something called DBI and it can be used to insert separated words
> into the database, instead
> of printing them. The bottom line is that perl is an excellent tool for
> parsing strings and all sorts of string
> manipulation.
>
> On 2003.11.20 22:39, Guang Mei wrote:
> > Hi:
> >
> > In my pl/sql program, I want to process each "word" in a string. The
> > string is selected from a varchar2(300) column. The delimit that separates
> > the words is not necessary space character. The definition of the delimit
> > in this program is set as
> >
> > 1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
> > and
> > 2. the character is not one of these: '-.,/<*>_'
> >
> > Now my program is basically checking each character, find the delimit, and
> > rebuild each word. After that I process each "word". The code looks like
> > this:
> >
> > -------
> > str := "This will be a long string with length upto 300 characters, it
> > may contain some invisible characters';
> > len := length(str)+1;
> > for i in 1..len loop
> > ch := substr(str,i,1);
> > if (not strings.isAlnum(ch) and instr('-.,/<*>_', ch)<1) then
> > if word is not null then
> > -- do some processing to variable word !
> > word := null; -- reset it
> > end if;
> > else
> > word := word || ch; -- concat ch to word
> > end if;
> > end loop;
> >
> > -------
> >
> > I think It's taking too long because it loops through each characters. I
> > hope I could find a way to speed it up. I don't have experiience in
> > owa_pattern, but I thought there might be a way to do it here:
> >
> > ----
> > str := "This will be a long string with length upto 300 characters, it
> > may contain some invisible characters';
> > newstr := str;
> > pos := 1;
> > while pos != 0 loop
> > pos := owa_pattern.amatch(newstr, 1, '\W'); -- how can I mask out
> > these '-.,/<*>_' ???
> > word := substr(newstr, 1, pos-1);
> > -- do some processing to variable word !
> > if pos != 0 then
> > newstr := substr(newstr, pos+1);
> > end if;
> > end loop;
> > ------
> >
> > My simple tests showed that owa_pattern call is much slower than direct
> > string manupilation. But I would like to try it in this case if I could
> > easily get the "wrods" from the string. Any suggestions?
> >
> > TIA.
> >
> > Guang
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Guang Mei
> > INET: gmei_at_incyte.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mgogala_at_adelphia.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Guang Mei
> INET: gmei_at_incyte.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Mladen Gogala
Oracle DBA

Note:
This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).