dbdump.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 DbDumpOptions
  def self.parse(args)
    options = OpenStruct.new
    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: dbdump [options]"
      optionParser.separator ""
      optionParser.separator "Required Options:"
            
      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   
    end
    
    optionParser.parse!(args)
    
    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 DbDump
  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 dump
    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]
      
      cursor = @database.exec("select * from #{@owner}.#{table}")
      values = cursor.fetch_hash
      while !values.nil?  
        dbutils.print_table_row(@owner, table, values)
        values = cursor.fetch_hash
      end
    end
  end
end

options = DbDumpOptions.parse(ARGV)
  
dbdump = DbDump.new( 
  options.user_name, 
  options.password, 
  options.tns_name, 
  options.owner,
  options.verbose)
dbdump.dump