show Oracle params

gistfile1.rb

#!/usr//bin/env ruby

##################################################################################
#
# Program Name: showParams.rb
# Author:  Sean Stephenson
# Course:  CS151B
# Date:  2010-02-21
#                                                                               
# showParams.rb will do the followings:                                          
# 1. running ShowParams without argument will display 
#    all documented params in sorted order
# 2. with -n as an argument, it will display the total 
#    number of documented params
# 3. with a regular expression as an argument, it will 
#    display all documented params matched with the regular 
#    expression
# 4. with -n and a regular expression as an argument, it 
#    will display total number of documented params
#    matched with the regular expression
# 5. with -u as an argument, it will display all of the 
#    undocumented params
# 6. with -u and -n as an argument, it will display the 
#    total number of undocumented params
# 7. with -u and a regular expression as an argument, 
#    it will display all undocumented params matched 
#    with the regular expression
# 8. with -u and a regular expression and -n as an 
#    argument, it will display total number of undocumented 
#    params matched with the regular expression
#
##################################################################################

#*********************************do_oracle(sql)**********************************
def do_oracle(sql)
   sql.chop! if sql[-1] == ';'
   cmd = <<-EOJ
       sqlplus -S {/nolog} AS SYSDBA <<-EOF 
          set echo off head off pagesize 0
          #{sql};
          set echo on head on pagesize 24
          quit
       EOF
   EOJ
   done = system(cmd)
   abort("access to Oracle failed\n") unless done
end


#**********************************listParams*************************************
def listParams
  puts "documented params: "
  str = "select name, value, type, isdefault from v\\$parameter"
  do_oracle(str)
end


#**********************************countParams************************************
def countParams
  print "total documented params: "
  str = "select count(name) from v\\$parameter"
  do_oracle(str)
end


#*********************************regExDisplay(match)*****************************
def regExDisplay(match) 
  puts "documented params matching regEx:"
  str = "select distinct name from v\\$parameter
         where regexp_like(name, '#{match}', 'i')
         order by name"
  do_oracle(str)
end



#*********************************regExCount(match)******************************
def regExCount(match)
  print "total documented params matching regexp: "
  str = "select count(name) from v\\$parameter
         where regexp_like(name, '#{match}', 'i')"
  do_oracle(str)
end


#**********************************listUndocumentedParams*************************
def listUndocumentedParams
  puts "undocumented params: "
  str = "select a.ksppinm parameter,
                a.ksppdesc description,
                b.ksppstvl session_value,
                c.ksppstvl instance_value
                from x\\$ksppi a, x\\$ksppcv b, x\\$ksppsv c
                where
                a.indx = b.indx and
                a.indx = c.indx and
                substr(a.ksppinm,1,1)='_'
                order by a.ksppinm;"
  do_oracle(str)
end


#**********************************countUndocumentedParams************************
def countUndocumentedParams
  print "total undocumented params: "
  str = "select count('a.ksppinm parameter')
                from x\\$ksppi a
                where
                substr(a.ksppinm,1,1)='_'
                order by a.ksppinm;"
  do_oracle(str)
end


#**********************************regExDisplayUndocumentedParams(match)**********
def regExDisplayUndocumentedParams(match)
  puts "undocumented params matching regex: "
  str = "select a.ksppinm parameter
                from x\\$ksppi a
                where
                regexp_like(a.ksppinm, '#{match}', 'i')
                order by a.ksppinm;"
  do_oracle(str)
end


#**********************************regExCountUndocumentedParams(match)*************
def regExCountUndocumentedParams(match)
  print "total undocumented params matching regex: "
  str = "select count('a.ksppinm parameter')
                from x\\$ksppi a
                where
                regexp_like(a.ksppinm, '#{match}', 'i')
                order by a.ksppinm;"
  do_oracle(str)
end







#*********************************GET USER INPUT: documented params**************
if ARGV[0] != '-u'
  case
  when ARGV[0] == nil
    listParams
  when ARGV[0] == '-n' && ARGV[1] == nil
    countParams
  when ARGV[0] =~ /\w/ && ARGV[1] == nil
    regExDisplay(ARGV[0]) 
  when ARGV[0] =~ /\w/ && ARGV[1] == '-n'
    regExCount(ARGV[0])
  end
end


#*********************************GET USER INPUT: undocumented params************
if ARGV[0] == '-u'
  case
  when ARGV[1] == nil
    listUndocumentedParams
  when ARGV[1] == '-n' && ARGV[2] == nil
    countUndocumentedParams
  when ARGV[1] =~ /\w/ && ARGV[2] == nil
    regExDisplayUndocumentedParams(ARGV[1])
  when ARGV[1] =~ /\w/ && ARGV[2] == '-n'
    regExCountUndocumentedParams(ARGV[1])
  end
end