#!/usr/bin/env ruby

require "dbi"
require 'getoptlong'

=begin

= bos (broadcast oracle select)

== USAGE

This is a Ruby script usefull to lunch a SQL statement to several Oracle
instances

USAGE: bos [--sid|-s XXX] [--sfile|-S XXX] [--query|-q XXX]  [--qfile|-Q XXX]  [--user|-u XXX]  [--ufile|-U XXX] [param1] [...] 

where
	[--sid|-s XXX]: XXX is a oracle instance
	[--sfile|-S XXX]: XXX is a file with the list of Oracle databases
	[--query|-q XXX]: XXX is a SQL statement
	[--qfile|-Q XXX]: XXX is a file with inside a SQL statement
	[--user|-u XXX]: XXX is username/password of the Oracle user
	[--ufile|-U XXX]: XXX is a file with the list of username/password

	param1, param2, ... are the parameters (like "#P1#=95") used inside the SQL statement


Examples:
	bos --sfile "/makalu/sapbasis/scripts/prod.txt" --query "select mandt from sapr3.t000" --ufile users

	bos --sid C11 --query "select mandt from sapr3.t000" --user sapr3/sap


Examples of SQL script:

-----------------------------------------------------------
extents.sqlp
-----------------------------------------------------------

		select
			SEGMENT_NAME,
			TABLESPACE_NAME,
			EXTENTS,
			MAX_EXTENTS,
			MAX_EXTENTS - EXTENTS free,
			NEXT_EXTENT,
			round(EXTENTS / MAX_EXTENTS * 100, 1)     perc
		from
			dba_segments
		where
			owner='SAPR3' and
			MAX_EXTENTS - EXTENTS <= #P1#
		order by
			SEGMENT_NAME

------------------------------------------------------------
ts.sqlp
------------------------------------------------------------

		select
			u.tablespace_name,
			round(u.used_mb, 2),
			round(f.free_mb, 2),
			round(u.used_mb + f.free_mb, 2) size_mb,
			round((u.used_mb * 100) /(u.used_mb + f.free_mb), 1) || '%' perc
		from
			(
			select
				tablespace_name,
				sum(bytes) / 1024 / 1024 used_mb
			from
				dba_segments
			group by
				tablespace_name
			) u, (
			select
				tablespace_name,
				sum(bytes) / 1024 / 1024  free_mb
			from
				dba_free_space
			group by
				tablespace_name
			) f
		where
			u.tablespace_name = f.tablespace_name and
			round((u.used_mb * 100) /(u.used_mb + f.free_mb), 1) > #P1# and
			round(f.free_mb, 2) < #P2#

------------------------------------------------------------------------
next.sql
------------------------------------------------------------------------

		select
			s.SEGMENT_NAME,
			s.TABLESPACE_NAME,
			s.EXTENTS,
			s.NEXT_EXTENT
		from
			dba_segments s
		where
			s.owner='SAPR3' and
			s.TABLESPACE_NAME <> 'PSAPTEMP' and
			not exists (
				select 1 from
					dba_free_space f
				where
					f.tablespace_name = s.tablespace_name
				and
					f.BYTES > s.NEXT_EXTENT
			)

------------------------------------------------------------------------


Example of sfile:

	P00
	P01
	P02
	P03
	P04
	PTB
	G20
	G18


Example of ufile:

	sapr3/sap
	system/manager



== REQUIREMENTS

I've tested this library with a Debian Linux 3.0 and
  * ruby 1.6.7
  * rubi-dbi 

== HISTORY

  * 2002-09-23 (Matteo)  
		I have ported my previous orasql.pl Perl script to Ruby.
		
		There is also a new
		parameter: --user (and -ufile)


== AUTHOR

E-MAIL: matteo.redaelli@libero.it

WEB:    http://digilander.iol.it/reda

== LICENSE

This package is free software; you can redistribute it and/or
modify it under the same terms as Perl itself, i.e., under the
terms of the "Artistic License" or the "GNU General Public License".

The C library at the core of this Perl module can additionally
be redistributed and/or modified under the terms of the
"GNU Library General Public License".


== DISCLAIMER

This package is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

See the "GNU General Public License" for more detail

=end

opts = GetoptLong.new(
  [ "--sid", "-s", GetoptLong::REQUIRED_ARGUMENT ],
  [ "--sfile", "-S", GetoptLong::REQUIRED_ARGUMENT ],
  [ "--query", "-q", GetoptLong::REQUIRED_ARGUMENT ],
  [ "--qfile", "-Q", GetoptLong::REQUIRED_ARGUMENT ],
  [ "--user", "-u", GetoptLong::REQUIRED_ARGUMENT ],
  [ "--ufile", "-U", GetoptLong::REQUIRED_ARGUMENT ]
)

sids =  []
users =  []
sql = ""

opts.each do |opt, arg|
  case opt
		when "--sid"
			sids.push( arg )
		when "--sfile"
			sids = sids.concat( IO.readlines(arg).collect {|x| x.to_s.chomp }  )
		when "--query"
			sql = arg
		when "--qfile"
			sql = IO.readlines(arg).collect {|x| x.to_s.chomp }.join("\n") 
		when "--user"
			users.push( arg )
		when "--ufile"
			users = users.concat( IO.readlines(arg).collect {|x| x.to_s.chomp }  )
		else
			$stderr.puts "Unexpected parameter:"
			$stderr.puts "Option: #{opt}, arg #{arg.inspect}"
  end
end

def usage
  $stderr.puts <<EOL 
	
Type "rd2 bos" to see the documentation 

EOL
	exit 1
end

def oraselect( sid, sql, users )

	begin 
	u = users.pop.split('/')
  user = u[0]
  pwd = u[1]
  dbh = DBI.connect("dbi:Oracle:#{sid}", user, pwd)
		dbh.select_all( sql ) do | row |
			p row
		end
		dbh.disconnect
	rescue
	  if ( not users.empty? )
			retry
		else
			$stderr.puts "Can't connect to #{sid}"
		end
	end
end


# Now I substitute the parameters into "sql" string

ARGV.each {|p|
	values = p.split('=')
	sql.gsub!( values[0],values[1] )
}

# Now I execute the "sql" statement in every "sid"

sids.each {|sid| 
	p sid
	oraselect( sid, sql, users.dup )
}