#!/usr/bin/perl -w

=head1 NAME

orasql.pl


=head1 AUTHOR

Matteo Redaelli
E-MAIL: matteo.redaelli@libero.it
WEB:    http://digilander.iol.it/reda


=head1 USAGE

orasql.pl [-s sid | l file1] -q file2 [param1=val1 ...]

where 
 sid is an oracle instance,
 file1 is a file containing a list of oracle instances,
 file2 is a file containing a sql statement
 param1,... are parameters inside the sql statement


=head1 DESCRIPTION

Useful to submit a SQL statement to several oracle instances. For instance,
I use it to find the fullest tablespaces and the table with few next extents
of my sap/oracle production systems.


=head1 MOTTO

Keep it small, fast and simple


=head1 MODIFIED

2001-11-01 Matteo
	The first beta of this script

2001-11-16 Matteo
	Added parameters using the standard module Getopt::Std

2001-11-26 Matteo
	Now it's possible to add parameters in the sql statement

2001-11-27 Matteo
	More comments

2001-11-28 Matteo
	The name of the script has changed because now it is possible to 
	execute DDL sql statement, too.


=head1 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".


=head1 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 details.

=cut

use DBI;
use Getopt::Std;

use strict;

sub usage()
{
	die <<END_USAGE

Usage: $0 [-s sid | -l file1] -q file2 [param1=val1 ...]
	
	sid:	is a oracle instance
	file1:	is a file containing a list of oracle instances
	file2:	is a file containing the SQL statement
	param1,val1 are paramerts inside the SQL statement


END_USAGE

}


my $sql = "";
my $param = "";
my $dbh;

sub ora_connect() 
{
	my $user = "SAPR3";
	my $inst = $_[0];
	print "SID=$inst\n";

	# Connect to database

	# CHANGE/ADD here the Oracle users/passwords...

	$dbh = DBI->connect( "dbi:Oracle:$inst", $user, "XXXXXX",
		    { AutoCommit => 0, RaiseError => 0, PrintError => 0 } )
	    or $dbh = DBI->connect( "dbi:Oracle:$inst", $user, "sap",
		{ AutoCommit => 0, RaiseError => 0, PrintError => 0 } )
	    or $dbh = DBI->connect( "dbi:Oracle:$inst", "system", "manager",
		{ AutoCommit => 0, RaiseError => 0, PrintError => 0 } )
	    or $dbh = DBI->connect( "dbi:Oracle:$inst", "sys", "change_on_install",
		{ AutoCommit => 0, RaiseError => 0, PrintError => 0 } );
	    
	return $dbh;
}

sub ora_exec_sql() 
{
	my $inst = $_[0];
	$dbh = &ora_connect( $inst ) 
	    or (warn "$DBI::errstr\n" and next);

	# Prepare the SELECT statement using a placeholder
	my $sth = $dbh->prepare( $sql )  || (warn "$DBI::errstr\n" and next);


	my ( @row, $acol );
	    $sth->execute() ||  (warn "$DBI::errstr\n" and next);

	# Note that the variable is in parenthesis to give an array context
	while ( ( @row ) = $sth->fetchrow_array ) {
		foreach $acol ( @row ) {
			print "$acol\t";
		}
		print "\n";
	    }

	$sth->finish;
	$dbh->disconnect;
}



my %opts;
my $inst = "ORCL";
my $FILE;

getopts("s:q:l:", \%opts);

my $filename_sql = $opts{'q'} or usage();

$opts{'l'} or $opts{'s'} or usage();

my $filename_sids = $opts{'l'};


my @sids =();

if ($opts{'s'} ) {
	push(@sids, $opts{'s'} ); 
}


# loading SQL statement

open($FILE,$filename_sql) || die("Cannot open file $filename_sql, bye!\n");

while (<$FILE>) {
	$sql = $sql . $_	
}
close( $FILE );

# loading list of oracle instances

if ($filename_sids) {
	open($FILE,$filename_sids) || 
		die("Cannot open file $filename_sids, bye!\n");

	while (<$FILE>) {
		 chomp;
		 push( @sids, $_);
	}
	close( $FILE );
}

# if there are any parameters I'll substitute them in the sql statement...

foreach $param ( @ARGV )  {
	my ($par_text, $par_val) = split("=", $param);
	$_ = $param;
	$sql =~ s/$par_text/$par_val/g;
	
#	print "$param: $par_text, $par_val\n";
};

#print "$sql\n";

# Now I have the SQL statement and the list of Oracle instances
# where I want to submit it.

foreach $inst ( @sids ) {
	&ora_exec_sql( $inst );
};