Nathan's Blog computer stuff

#!/usr/bin/perl
 
# 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}) && $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->connect($connstr, $dbuname, $dbpw, {RaiseError=>1, AutoCommit=>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->{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->prepare($sql);
 
$sth1->execute(uc($schema));
 
while (my $row = $sth1->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";
	$sth1->finish();
	$dbh1->disconnect;
 
	exit 1;
  }
}
 
$sth1->finish();
$dbh1->disconnect;
 
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 activestate.com
  # 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 //,
					 '!#$%&()*+,-_./:;<=> ?@[\]^');
 
  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;
	  next;
	}
	if ($ch eq "\x15") {	# ^U
	  print "\b \b" x length $passwd;	# back 1 for each char
	  $passwd = '';
	  next;
	}
	if (not exists $legal_clear{$ch}) {
	  print "\n'$ch' not a legal password character\n";
	  print 'Password: ', "*" x length $passwd; # retype *'s
	  next;
	}
	$passwd .= $ch;
	print '*';
  }
  print "\n";
  ReadMode ('restore');
  return $passwd;
}
END {
  ReadMode ('restore');
 
}