Nathan's Blog computer stuff

October 1, 2008

Script for doing regex searches on view definitions in oracle

Filed under: oracle,perl — Tags: , , , — @ 5:40 pm

This is a script that allows you to use the power of perl regexes to search through view definitions in order to look for matches. This is useful for me because we have one schema in particular with over 400 views and this is great for tracking down dependencies on tables, functions etc.


  • You must have a working oracle client install, ORACLE_HOME must be set and you must have a tnsnames.ora file in the appropriate location.
  • You must have perl, DBI and DBD::Oracle installed and working
  • You need Term::ReadKey CPAN module installed (most distros will include this by default)

I have no idea if this will prove useful to anyone, but in case it is, here you go.

# this takes as arguments the username /password / connection identifier,
# in typical oracle util fashion: username[/pass][@conn_id]
# the schema to search under (can be a sql wildcard w/ %'s), and a search
# term that is basically a perl regex.  anything discovered to contain the
# search term will be displayed.
use DBI;
use strict;
use Term::ReadKey; 
if (@ARGV != 3) {
  my $prgname = $0;
  $prgname =~ s!^.*/([^/]+)$!$1!;
  print STDERR "Usage:  $prgname [/PASSWORD][\@CONNECTION IDENTIFIER]  \n";
  exit 1;
my ($unpw, $schema, $searchterm) = @ARGV;
my $dbuname = "";
my $dbpw = "";
my $connstr = "";
my $db_sid = "";
$schema =~ s/[\"\']//g;
$searchterm =~ s/[\"\']//g;
# take off any leading or trailing / from the search regex, if present.  we'll add our own.
$searchterm =~ s!^/!!g;
$searchterm =~ s!/$!!g;
# peel off the connection identifier and password from cli if they are present
# grab the sid, which will be preceded by an '@' symbol
# see if it's on the command line
if ($unpw =~ s/@([^@]+)$//) {
  $db_sid = $1;
#otherwise, check the TWO_TASK environment var
elsif( exists( $ENV{TWO_TASK}) &amp;&amp; $ENV{TWO_TASK} ne "" ) {
  $db_sid = $ENV{TWO_TASK};
else {
  # woops
  print STDERR "No connection identifier specified.  Either define TWO_TASK or pass a connection identifier on the command line.\n";
  exit 1;
if ($unpw =~ s!/(.*)$!!) {
  # grab the password from the cli if there is a slash
  $dbpw = $1;
else {
  #otherwise, prompt for it
  $dbpw = get_passwd();
# all that should be left in unpw is the username
$dbuname = $unpw;
my $connstr = "dbi:Oracle:$db_sid";
#connect to the db
my $dbh1 = DBI-&gt;connect($connstr, $dbuname, $dbpw, {RaiseError=&gt;1, AutoCommit=&gt;0}) or die $DBI::errstr;
# this is so that we can grab the view definitions from the catalog, they use
# LONG as their type unfortunately.  hopefully there are no views bigger than 10M
$dbh1-&gt;{LongReadLen} = 10485760;
# allow for schema to be a wildcard, such as %
my $sql = "select view_name, text, owner from all_views where owner like ? order by owner, view_name";
my $sth1 = $dbh1-&gt;prepare($sql);
while (my $row = $sth1-&gt;fetchrow_arrayref()) {
  my ($view_name, $view_defn, $owner) = @$row;
  $view_defn = whitespace_deleter($view_defn);
  # look for the regex
  eval {
	if ($view_defn =~ /$searchterm/is) {
	  print "$owner.$view_name matches\n";
  if ($@) {
	## the regex is b0rked
	print "/$searchterm/ is not a valid regex\n";
	exit 1;
sub whitespace_deleter {
  my ($inp) = @_;
  $inp = uc($inp);
  $inp =~ s/\s+/ /gs;
  return $inp;
sub get_passwd {
  # lifted this from perl-unix-users mailing list archives on
  # Thanks to Bill Luebkert
  # lets you prompt for a password
  # and echo stars to the screen, returns the password
  my $passwd = "";
  my @legal_clear = ('a'..'z', 'A'..'Z', '0'..'9', split //,
					 '!#$%&amp;()*+,-_./:;&lt;=&gt; ?@[\]^');
  my %legal_clear; foreach (@legal_clear) { $legal_clear{$_} = 1; }
  $| = 1;	# unbuffer stdout to force unterminated line out
  print "Password: ";
  ReadMode ('cbreak');
  my $ch = '';
  while (defined ($ch = ReadKey ())) {
	last if $ch eq "\x0D" or $ch eq "\x0A";
	if ($ch eq "\x08") {	# backspace
	  print "\b \b" if $passwd;	# back up 1
	  chop $passwd;
	if ($ch eq "\x15") {	# ^U
	  print "\b \b" x length $passwd;	# back 1 for each char
	  $passwd = '';
	if (not exists $legal_clear{$ch}) {
	  print "\n'$ch' not a legal password character\n";
	  print 'Password: ', "*" x length $passwd; # retype *'s
	$passwd .= $ch;
	print '*';
  print "\n";
  ReadMode ('restore');
  return $passwd;
  ReadMode ('restore');
Article Name
Script for doing regex searches on view definitions in oracle
This is a script that allows you to use the power of perl regexes to search through view definitions in order to look for text matches.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress