Re: regular expression and v$sql question

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Fri, 1 Jul 2011 10:53:36 -0400
Message-ID: <BANLkTi=oevm3+q-krbxKmd5gFFoWxw85Qg_at_mail.gmail.com>

Thank you. I did not think to look at regular expression documentation. I was looking at the oracle documentations and people's examples (they were all pretty similiar to oracles examples). This is very helpful.

On Wed, Jun 29, 2011 at 12:03 PM, Nigel Thomas < nigel.cl.thomas_at_googlemail.com> wrote:

> >How do I find the next word (not just the character, I want the
> tablename) after a previous word. Some I don't care about spaces, >but I
> want to find the next word (table name in this case) after the word INTO?
>
> For your first question - Have a look at Using Regular Expressions in *
> Oracle* Database<http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm>eg
> select columns from v$sql ... where regexp_like(lower(sql_text),
> 'insert\s+into\s+tablename')
>
> Get to know the character classes - \s+ stands in for any number of white
> space characters (it's a Perl character class - you could also use the
> equivalent Posix character class [:space:] - see
> http://www.regular-expressions.info/posixbrackets.html).
>
> You can also match on word boundaries eg '\btablename\b' would match
> against your tablename but not against anothertablename. However be aware
> that words are alphanumeric so I think another_tablename would count as 2
> words. So you may need a more complex regex that allows for the tablename to
> start after spaces and to be finished with a space, or an open bracket.
>
> And of course it could be insert into schema.tablename and there are other
> variants; insert into ... select from, insert into .., (column list) values
> (...), insert into ... values (...) etc.
>
> Hope that helps
>
> Regards Nigel
>
>
>

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

Received on Fri Jul 01 2011 - 09:53:36 CDT