Re: regular expression and v$sql question

From: Nigel Thomas <nigel.cl.thomas_at_googlemail.com>
Date: Wed, 29 Jun 2011 17:03:38 +0100
Message-ID: <BANLkTi=je6k1PPBQ6Q7i39vKSyM6B11e2Q_at_mail.gmail.com>

>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 Wed Jun 29 2011 - 11:03:38 CDT