dbgrep.rb

require 'oci8'
require 'ostruct'
require 'optparse'

class DatabaseUtils
  def print_table_row(owner, table_name, column_data)
    output = "#{owner}.#{table_name}: {"
    
    index = 0
    first = true
    column_data.each do |key, value|
      if value.nil?
        value = "(null)"
      end
      output << ", " if !first
      output << "{#{key}, #{value}}"      
      first = false
      index += 1
    end
    
    output << "}"
    puts output
  end 
end

class DbGrepOptions
  def self.parse(args)
    options = OpenStruct.new
    options.pattern = nil
    options.tns_name = nil
    options.user_name = nil
    options.password = nil
    options.owner = nil
    options.verbose = false
    
    optionParser = OptionParser.new do |optionParser|
      optionParser.banner = "Usage: dbgrep [options]"
      optionParser.separator ""
      optionParser.separator "Required Options:"
      
      optionParser.on("-e pattern", "--expression pattern", "The regular expression") do |pattern|
        if !options.pattern.nil?
          puts "Error: only one pattern may be specified."
          puts optionParser
          exit
        end
        
        options.pattern = pattern
      end
      
      optionParser.on("-d tns_name", "--database tns_name", "The TNS_NAME for the Oracle database to connect to.") do |tns_name|
        if !options.tns_name.nil?
          puts "Error: only one tns name may be specified."
          puts optionParser
          exit
        end
      
        options.tns_name = tns_name
      end
      
      optionParser.on("-u user_name", "--username user_name", "The database user name") do |user_name|
        if !options.user_name.nil?
          puts "Error: only one user name may be specified."
          puts optionsParser
          exit
        end
        
        options.user_name = user_name
      end
      
      optionParser.on("-p password", "--password password", "The database password") do |password|
        if !options.password.nil?
          puts "Error: only one password may be specified."
          puts optionParser
          exit
        end
        
        options.password = password
      end
      
      optionParser.on("-o owner", "--owner owner", "The database owner") do |owner|
        if !options.owner.nil?
          puts "Error: only one owner may be specified."
          puts optionParser
          exit
        end
        
        options.owner = owner
      end   
      
      optionParser.separator "Optional Options:"
      
      optionParser.on("--verbose", "Displays detailed informtation") do
        if options.verbose
          puts "Error: verbose may on be specified once."
          puts optionParser
          exit
        end
        
        options.verbose = true
      end
    end
    
    optionParser.parse!(args)
    
    required(optionParser, 'pattern', options.pattern)
    required(optionParser, 'user name', options.user_name)
    required(optionParser, 'password', options.password)
    required(optionParser, 'owner', options.owner)
    required(optionParser, 'database tns name', options.tns_name)
    
    options
  end
  
  private 
  
  def self.required(optionParser, name, value)
    if value.nil?
      puts "Error: #{name} must be specified"
      puts optionParser
      exit
    end
  end
end


class DbGrep
  def initialize(user_name, password, tns_name, owner, verbose = false)  
    @database = OCI8.new(user_name, password, tns_name)
    @owner = owner.upcase
    @verbose = verbose
  end
  
  def grep(pattern)
    expression = Regexp.new(pattern)
    
    dbutils = DatabaseUtils.new

    @database.exec("select distinct table_name from all_all_tables where owner = '#{@owner}'") do |table_name_row|
      table = table_name_row[0]
      
      puts "Checking table #{@owner}.#{table}" if @verbose
      start_time = Time.now
      cell_count = 0
      cursor = @database.exec("select * from #{@owner}.#{table}")
      values = cursor.fetch_hash
      while !values.nil?  
        values.each do |key, value|
          if value.to_s =~ expression   
            dbutils.print_table_row(@owner, table, values)
          end
          cell_count += 1
        end
        values = cursor.fetch_hash
      end
      end_time = Time.now
      puts "Checked #{cell_count} cells in #{cursor.row_count} rows in #{@owner}.#{table} in #{end_time - start_time} seconds" if @verbose
    end
  end
end

options = DbGrepOptions.parse(ARGV)
  
dbgrep = DbGrep.new( 
  options.user_name, 
  options.password, 
  options.tns_name, 
  options.owner,
  options.verbose)
dbgrep.grep(options.pattern)