Re: regular expressions in Perl and Oracle/SQL

In article <37BD0971.B8A5CEED_at_infineon.com>,   Philippe Emeriau <Philippe.Emeriau_at_infineon.com> wrote:
>
>
> SZEKERES Istvan wrote:
>
> > In article <37BC1D7B.3F854DDB_at_infineon.com>, Philippe Emeriau wrote:
> > >I would like to know if there is somewhere in Perl a function that
> > >computes a regular expression and gives the result stored inside a
> > >string ?
> > >Example : the regexp is "foo[1-6]", and I would like to store the
result
> > >"foo1 foo2 foo3 foo4 foo5 foo6" in a variable.
> >
> > Tell me please what value do you expect for the regexp "a.*b" :-)
> >
> > Pista
>
> Well, I have to give you more details on my requirements, so that you
can
> understand now why I have asked such question that seems to be so
silly … ;-)
>
> In fact, I have to provide to a given user (who is an Electronic
Design
> Automation engineer) a Perl API (a Perl package) that will provide a
full
> interface to data stored inside Oracle.
> The user doesn’t care about Oracle, SQL, and all that software stuff,
what he
> only knows is a file, with a given grammar, where he provides some
data to be
> stored in the DB (through another piece of software not mentionned
here !).
> This grammar uses some keywords to define some kind of sections and
> attributes, and the user provides some values for each attribute, and
these
> sections, attributes and values are mapped in the DB via tables and
views.
>
> So, with the Perl API, the user wants to retrieve the stored data, by
> providing for example the name of a section and a regular expression
defining
> a set of attributes. I believe Oracle does not handle correctly such
regexps
> (I was not able to find anything on the subject). That’s why I would
like to
> process inside the Perl API the provided regexp to compute the full
list of
> required attributes, and then build an SQL query with all these
attribute
> names (after maybe a mapping to real names of tables, views, …). The
query is
> then sent to the Oracle server through the Perl DBI/DBD packages, I
get in
> return a specific result, and I have just after to create a specific
Perl
> hash map that is used by the final user …
>
> I hope I was clear in my explaination …
> Maybe you could suggest me some other way to do it, because I’m
really stuck
> with this problem !!!
> Thanks in advance
> Philippe
>
> Philippe EMERIAU
> Senior Software Engineer
> Infineon Technologies Sophia Antipolis
> Gaïa, 2600 Route des Crêtes
> F-06560 Sophia Antipolis – FRANCE
> WARNING : NEW EMAIL !!!
> Email: Philippe.Emeriau_at_infineon.com
> Tel: (+33)(0)4 92 38 37 21
> Fax: (+33)(0)4 92 38 37 99
>

ORACLE supports a very simple set of reqular expressions via the LIKE operator.

To solve your problem, You really have to change your basic assumption. Rather than generating all possible values from a regular expression (which is impossible), you may have to fetch all the rows, and filter them via PERL.

Depending how this regular expression of yours is built, you might be able to do this in parts. If the RegEx has a part that identifies the tables, handle that first. Then the columns, and finally the rows. You can generate these selects on the fly in PERL to send to the database. Without knowing your data model, or the range of flexibility you want, I really cannot suggest much more.

But you have got to drop the mindset of generating all possible values.

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Aug 20 1999 - 11:19:30 CDT